Re: postgres 9 query performance

On Fri, Jan 28, 2011 at 10:30 AM, yazan suleiman
<yazan(dot)suleiman(at)gmail(dot)com> wrote:
> I am evaluating postgres 9 to migrate away from Oracle.  The following query
> runs too slow, also please find the explain plan:
> ****************************************************************
> explain analyze select DISTINCT EVENT.ID, ORIGIN.ID AS
> ORIGINID,EVENT.PREFERRED_ORIGIN_ID AS PREFERRED_ORIGIN,
> EVENT.CONTRIBUTOR, ORIGIN.TIME, ORIGIN.LATITUDE, ORIGIN.LONGITUDE,
> ORIGIN.DEPTH,ORIGIN.EVTYPE,
> "Total runtime: 17799.669 ms"
> ****************************************************************
> This query runs in Oracle in 1 second while takes 16 seconds in postgres,
> The difference tells me that I am doing something wrong somewhere.  This is
> a new installation on a local Mac machine with 12G of RAM.

Try turning it into a group by instead of a distinct. i.e.

select a,b,c,d from xyz group by a,b,c,d

and see if it's faster. There is some poor performance on large data
sets for distinct. Don't know if they got fixed in 9.0 or not, if not
then definitely try a group by and see.