aggregate on zero rows slow?

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