I found this same problem. It's the "group by" that is the killer.
PostgreSQL's implementation of "group by" requires a sort of the entire
table on the group by criteria, even if there is an index on the group
by column. It's to bad this caused us not to be able to use Postgresql
for a project.
> -----Original Message-----
> From: snpe [mailto:snpe@infosky.net]
> Sent: Tuesday, May 15, 2001 11:59 AM
> To: Stephan Szabo
> Cc: PostgreSQL-General
> Subject: Re: [GENERAL] Performance aggregates
>
>
> On Tuesday 15 May 2001 17:28, Stephan Szabo wrote:
> > On Tue, 15 May 2001, snpe wrote:
> > > Table e_kalkn have 4668 rows and e_kalkns 101170 rows.
> > >
> > > Query :
> > >
> > > select roba,sum(izn)
> > > from e_kalkn k,e_kalkns ks
> > > where k.id=ks.id
> > > group by roba
> > > order by roba
> > >
> > > is 2.5 times faster on one commercial database (there are tests on
> > > Internet that say 'Postgresql is faster than that database).
> > > I can't say which database it is.
> >
> > Have you run vacuum analyze (since loading the data) and
> what does explain
> > show for the query. Also, what version are you using?
>
> I have run :
>
> vacuumdb --analyze -v -d mytest
>
> I try index on column roba in table e_kalkn, but all is same.
>
> This is explain :
> psql:up1:4: NOTICE: QUERY PLAN:
>
> Aggregate (cost=11132.18..11286.42 rows=3085 width=32)
> -> Group (cost=11132.18..11209.30 rows=30849 width=32)
> -> Sort (cost=11132.18..11132.18 rows=30849 width=32)
> -> Hash Join (cost=121.35..8831.95 rows=30849
> width=32)
> -> Seq Scan on e_kalkns ks (cost=0.00..2041.10
> rows=101710 width=16)
> -> Hash (cost=109.68..109.68 rows=4668 width=16)
> -> Seq Scan on e_kalkn k
> (cost=0.00..109.68
> rows=4668 width=16)
>
> EXPLAIN
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
This email message is for the sole use of the intended recipient(s) and may
contain proprietary and confidential information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and destroy all copies
of the original message. Thank you
