Thanks that seems to make the query 10-15% faster :) Cheers jeremy
-----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, 18 January 2011 9:24 a.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Possible to improve query plan? Jeremy Palmer <jpal...@linz.govt.nz> writes: > I've come to a dead end in trying to get a commonly used query to > perform better. > EXPLAIN > SELECT * FROM ( > SELECT > row_number() OVER (PARTITION BY id ORDER BY _revision_created DESC) > as row_number, > * > FROM > version_crs_coordinate_revision > WHERE ( > (_revision_created <= 16 AND _revision_expired > 16 AND > _revision_expired <= 40) OR > (_revision_created > 16 AND _revision_created <= 40) > ) > ) AS T > WHERE row_number = 1; If I'm not mistaken, that's a DB2-ish locution for a query with DISTINCT ON, ie, you're looking for the row with highest _revision_created for each value of id. It might perform well on DB2, but it's going to mostly suck on Postgres --- we don't optimize window-function queries very much at all at the moment. Try writing it with DISTINCT ON instead of a window function, like so: SELECT DISTINCT ON (id) * FROM version_crs_coordinate_revision WHERE ( (_revision_created <= 16 AND _revision_expired > 16 AND _revision_expired <= 40) OR (_revision_created > 16 AND _revision_created <= 40) ) ORDER BY id, _revision_created DESC; You could also experiment with various forms of GROUP BY if you're loath to use any Postgres-specific syntax. regards, tom lane ______________________________________________________________________________________________________ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. ______________________________________________________________________________________________________ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
