Re: Really SLOW using GROUP BY ...!?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM>
Cc: "'herve(at)elma(dot)fr'" <herve(at)elma(dot)fr>, pgsql-general(at)postgresql(dot)org
Subject: Re: Really SLOW using GROUP BY ...!?
Date: 2000-11-08 21:26:44
Message-ID: 16754.973718804@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM> writes:
> Try
> select sum(points) from gains where idcond >= _minimum_id_cond_value_
> group by idcond;

> to see if forced index usage will help. Unfortunately, PG will anyway
> try to sort result before grouping,

I beg your pardon?

regression=# set enable_seqscan TO on;
SET VARIABLE
regression=# explain select sum(unique1) from tenk1 group by hundred;
NOTICE: QUERY PLAN:

Aggregate (cost=997.39..1047.39 rows=1000 width=8)
-> Group (cost=997.39..1022.39 rows=10000 width=8)
-> Sort (cost=997.39..997.39 rows=10000 width=8)
-> Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=8)

EXPLAIN
regression=# set enable_seqscan TO off;
SET VARIABLE
regression=# explain select sum(unique1) from tenk1 group by hundred;
NOTICE: QUERY PLAN:

Aggregate (cost=0.00..1119.88 rows=1000 width=8)
-> Group (cost=0.00..1094.88 rows=10000 width=8)
-> Index Scan using tenk1_hundred on tenk1 (cost=0.00..1069.88 rows=10000 width=8)

EXPLAIN

Unfortunately neither of these plans is likely to be especially speedy
on ~3 million rows. The index scan will just thrash the disk, unless
the table has been clustered recently --- and given the deficiencies of
our CLUSTER implementation, I'd hesitate to recommend using it.

I have a personal TODO item to see about implementing group + aggregate
with a hash table of active aggregate values, per a suggestion recently
from devik(at)cdi(dot)cz(dot) That would allow this query to be done with a
sequential scan and no sort, which is probably what Oracle is doing.
Won't happen for 7.1 though ...

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2000-11-08 21:27:34 Re: VACUUM AND VACUUM ANALYSE
Previous Message Wade D. Oberpriller 2000-11-08 21:13:50 Built-in Postgres Types as shown in Chap. 4 Programmer's Guide