Re: Major differences between oracle and postgres performance - what can I do ?

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: >
=?iso-8859-1?q?Gary=20Cowell?=
> <gary_cowell(at)yahoo(dot)co(dot)uk> writes:
> > So it seems the idea that oracle is dropping
> duplicate
> > rows prior to the sort when using distinct may
> indeed
> > be the case.
>
> Okay. We won't have any short-term solution for
> making DISTINCT do that,
> but if you are on PG 7.4 you could get the same
> effect from using
> GROUP BY: instead of
> select distinct version from vers where version is
> not null
> try
> select version from vers where version is not null
> group by version
> You should get a HashAggregate plan out of that, and
> I'd think it'd be
> pretty quick when there are not many distinct values
> of version.
>

Yeah out of the half million rows there are only ever
going to be 500 or so distinct values.

I do indeed get such a plan. It's much faster that
way. Down to 16 seconds. I'll get the chap to rewrite
his app to use group by instead of distinct.

Thanks (everyone) for the top class help!

___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com