pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: A Palmblad <adampalmblad@yahoo.ca>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: SLOW query with aggregates
Date: 23 Mar 2004 22:42:20 -0500
Message-ID: <878yhqx65f.fsf@stark.xeocode.com> (raw)
In-Reply-To: <003201c41111$f4dc96f0$97019696@AERS04>
References: <003201c41111$f4dc96f0$97019696@AERS04>


"A Palmblad" <adampalmblad@yahoo.ca> writes:

>  GroupAggregate  (cost=0.00..338300.34 rows=884 width=345) (actual
> time=86943.272..382718.104 rows=3117 loops=1)
>    ->  Merge Join  (cost=0.00..93642.52 rows=1135610 width=345) (actual
> time=0.148..24006.748 rows=1120974 loops=1)

I think the reason you're getting a GroupAggregate here instead of a
HashAggregate is that the MergeJoin is already producing the records in the
desired order, so the GroupAggregate doesn't require an extra sort, ie, it's
effectively free.

You might be able to verify this by running the query with 

enable_indexscan = off  and/or  enable_mergejoin = off

some combination of which might get the planner to do a seqscan of the large
table with a hash join to the small table and then a HashAggregate.

If you're reading a lot of the large table the seqscan could be a little
faster, not much though. And given the accurate statistics guesses here the
planner may well have gotten this one right and the seqscan is slower. Can't
hurt to be verify it though.

-- 
greg




view thread (3+ messages)

Message-ID: <878yhqx65f.fsf@stark.xeocode.com>
Permalink:  ../878yhqx65f.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/878yhqx65f.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, adampalmblad@yahoo.ca
  Subject: Re: SLOW query with aggregates
  In-Reply-To: <878yhqx65f.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox