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

HI,

"Mikheev, Vadim" a écrit :
>
> > But when I do :
> > select sum(points) from gains group by idcond;
> >
> > With Oracle : 22 sec
> > With PostGreSQL : about 3 minutes !!!
>
> 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, but probably this trick will help
> somehow. Also, use -S 2048 (or more) backend arg to increase sort
> memory size.

I'm using -S 512000 ;))

The result I have is not good ;)

pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

:(

So I vacuum analyze my table ...

Then I get : exactly the same result : about 3 min !

and my table have 2729276 records

The EXPLAIN of you request give :
NOTICE: QUERY PLAN:

Aggregate (cost=488140.30..501773.03 rows=272655 width=8)
-> Group (cost=488140.30..494956.67 rows=2726547 width=8)
-> Sort (cost=488140.30..488140.30 rows=2726547 width=8)
-> Seq Scan on gains (cost=0.00..62890.95 rows=2726547
width=8)

And with my classical request :

NOTICE: QUERY PLAN:

Aggregate (cost=481763.55..495409.93 rows=272928 width=8)
-> Group (cost=481763.55..488586.74 rows=2729276 width=8)
-> Sort (cost=481763.55..481763.55 rows=2729276 width=8)
-> Seq Scan on gains (cost=0.00..56067.76 rows=2729276
width=8)

Seems to have no effect !?

--
Hervé Piedvache

Elma Ingenierie Informatique
6, rue du Faubourg Saint-Honoré
F-75008 - Paris - France
http://www.elma.fr
Tel: +33-1-44949901
Fax: +33-1-44949902
Email: herve(at)elma(dot)fr