Thread: aggregate on zero rows slow?
I am trying to figure out why given the same query:
SELECT MAX(fromoctets),MAX(tooctets)
FROM stats,trans
WHERE stats.id=stats_id
AND firsttimei=9224192
AND sourcepeeraddress='xxx.yyy.zzz.38'
AND flowindex=4701
AND timeslice<'15:20:00 Wed 1 May 2002';
Aggregate (cost=57.36..57.36 rows=1 width=16)
-> Nested Loop (cost=0.00..57.36 rows=1 width=16)
-> Index Scan using firsttimei_idx on trans (cost=0.00..54.33 rows=1 width=12)
-> Index Scan using stats_pkey on stats (cost=0.00..3.02 rows=1 width=4)
the select appears to take much longer to complete if the WHERE clause
matches zero rows, than if it matches some rows.
Is this possible, or just chance?
Cheers,
Patrick
On Wed, 1 May 2002 21:52:47 +0100 "Patrick Welche" <prlw1@newn.cam.ac.uk> wrote: > I am trying to figure out why given the same query: > > SELECT MAX(fromoctets),MAX(tooctets) > FROM stats,trans > WHERE stats.id=stats_id > AND firsttimei=9224192 > AND sourcepeeraddress='xxx.yyy.zzz.38' > AND flowindex=4701 > AND timeslice<'15:20:00 Wed 1 May 2002'; > > Aggregate (cost=57.36..57.36 rows=1 width=16) > -> Nested Loop (cost=0.00..57.36 rows=1 width=16) > -> Index Scan using firsttimei_idx on trans (cost=0.00..54.33 rows=1 width=12) > -> Index Scan using stats_pkey on stats (cost=0.00..3.02 rows=1 width=4) > > > the select appears to take much longer to complete if the WHERE clause > matches zero rows, than if it matches some rows. How "much longer" are we talking about? When the query is fast, how many rows are returned, on average? Can you post the output of EXPLAIN ANALYZE for both the fast query (where the aggregate operates on > 0 rows), and the slow one? My guess would be that different query plans are being chosen, the aggregate is just incidental -- but we'll need more information to be sure. BTW, this is 7.2, right? Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> ... the select appears to take much longer to complete if the WHERE clause
> matches zero rows, than if it matches some rows.
Doesn't make any sense to me. Are you sure the same plan is being
chosen in both cases? If so, could you post a complete example?
regards, tom lane
On Wed, May 01, 2002 at 10:21:48PM -0400, Tom Lane wrote: > Patrick Welche <prlw1@newn.cam.ac.uk> writes: > > ... the select appears to take much longer to complete if the WHERE clause > > matches zero rows, than if it matches some rows. > > Doesn't make any sense to me. Are you sure the same plan is being > chosen in both cases? If so, could you post a complete example? Yes, same plan both cases. Thanks for the answer: as it doesn't make any sense to anyone, it must mean there is some corruption somewhere as a result of Monday morning's powercut => I'll look elsewhere. Thanks, Patrick
On Thu, 2 May 2002, Patrick Welche wrote: > On Wed, May 01, 2002 at 10:21:48PM -0400, Tom Lane wrote: > > Patrick Welche <prlw1@newn.cam.ac.uk> writes: > > > ... the select appears to take much longer to complete if the WHERE clause > > > matches zero rows, than if it matches some rows. > > > > Doesn't make any sense to me. Are you sure the same plan is being > > chosen in both cases? If so, could you post a complete example? > > Yes, same plan both cases. Thanks for the answer: as it doesn't make any > sense to anyone, it must mean there is some corruption somewhere as a result > of Monday morning's powercut => I'll look elsewhere. Could it possibly be that, since it can't find any rows, it's fully exhausting the indexes looking for the values it wants? I mean, a match would presumably be found in the indexes before hitting the end, but a non-match wouldn't. If the index is big enough, I could see a query that returned no results taking longer than one which does. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Administrator | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : www.townnews.com | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
On Thu, May 02, 2002 at 12:32:06PM +0100, Patrick Welche wrote: > On Wed, May 01, 2002 at 10:21:48PM -0400, Tom Lane wrote: > > Patrick Welche <prlw1@newn.cam.ac.uk> writes: > > > ... the select appears to take much longer to complete if the WHERE clause > > > matches zero rows, than if it matches some rows. > > > > Doesn't make any sense to me. Are you sure the same plan is being > > chosen in both cases? If so, could you post a complete example? > > Yes, same plan both cases. Thanks for the answer: as it doesn't make any > sense to anyone, it must mean there is some corruption somewhere as a result > of Monday morning's powercut => I'll look elsewhere. And indeed a recompile/dump/initdb/restore cured this strange behaviour. The only sign of a database problem though, apart from the speed, was a single occurance of "Tuple could not find parent"... Cheers, Patrick