Re: Possible to improve query plan?

От: Jeremy Palmer
Тема: Re: Possible to improve query plan?
Дата: ,
Msg-id: 666FB8D75E95AE42965A0E76A5E5337E06D2C686B5@prdlsmmsg01.ad.linz.govt.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Possible to improve query plan?  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Possible to improve query plan?  (Jeremy Palmer, )
 Re: Possible to improve query plan?  (Andy Colson, )
  Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )
 Re: Possible to improve query plan?  (Jayadevan M, )
 Re: Possible to improve query plan?  (Tom Lane, )
  Re: Possible to improve query plan?  (Jeremy Palmer, )
  Re: Possible to improve query plan?  (Mladen Gogala, )

Thanks that seems to make the query 10-15% faster :)

Cheers
jeremy

-----Original Message-----
From: Tom Lane [mailto:]
Sent: Tuesday, 18 January 2011 9:24 a.m.
To: Jeremy Palmer
Cc: 
Subject: Re: [PERFORM] Possible to improve query plan?

Jeremy Palmer <> 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 ) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________


В списке pgsql-performance по дате сообщения:

От: Fernando Hevia
Дата:
Сообщение: Re: Problem with query
От: Achilleas Mantzios
Дата:
Сообщение: Re: "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2