Skip site navigation (1) Skip section navigation (2)

Re: Optimising a query

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Richard Huxton" <dev(at)archonet(dot)com>
Cc: "Paul Lambert" <paul(dot)lambert(at)reynolds(dot)com(dot)au>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Optimising a query
Date: 2007-12-19 09:50:29
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> Also, try replacing the DISTINCT with GROUP BY. The code path for DISTINCT
> unfortunately needs a bit of cleaning up and isn't exactly equivalent to GROUP
> BY. In particular it doesn't support hash aggregates which, if your work_mem
> is large enough, might work for you here.

Sorry, strike that last suggestion. I was looking at the plan and forgot that
the query had DISTINCT ON. It is possible to replace DISTINCT ON with GROUP BY
but it's not going to be faster than the DISTINCT ON case since you'll need
the sort anyways.

Actually it's possible to do without the sort if you write some fancy
aggregate functions but for this large a query that's going to be awfully

  Gregory Stark
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

In response to

pgsql-performance by date

Next:From: Stuart BishopDate: 2007-12-19 12:04:11
Subject: Dual core Opterons beating quad core Xeons?
Previous:From: Gregory StarkDate: 2007-12-19 09:47:24
Subject: Re: Optimising a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group