Re: aggregate on zero rows slow?

On Wed, 1 May 2002 21:52:47 +0100
"Patrick Welche" <prlw1(at)newn(dot)cam(dot)ac(dot)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(at)rogers(dot)com>
PGP Key ID: DB3C29FC