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

--- Tom Lane <tgl@sss.pgh.pa.us> wrote: >
=?iso-8859-1?q?Gary=20Cowell?=
> <gary_cowell@yahoo.co.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
expressyourself http://uk.messenger.yahoo.com