Re: Is PostgreSQL 9.3 using indexes for pipelined top-N window function queries?

On Fri, Feb 14, 2014 at 7:35 PM, Behrang Saeedzadeh <behrangsa@gmail.com> wrote:
> Hi,
>
> I just stumbled upon this article from 2012 [1], according to which
> (emphasis mine):
>
> Window functions offer yet another way to implement pagination in SQL. This
> is a flexible, and above all, standards-compliant method. However, only SQL
> Server and the Oracle database can use them for a pipelined top-N query.
> PostgreSQL does not use indexes for those queries and therefore executes
> them very inefficiently. MySQL does not support window functions at all.
>
>
> Is this still the case? Or is PostgreSQL 9.3 capable to execute suchlike
> queries efficiently?

oracle:
SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC;

postgres:
SELECT * FROM sales s
WHERE (sale_date, sale_id) < (last_date, last_Id)
ORDER BY sale_date DESC, sale_id DESC
LIMIT 10;

The postgres variant is superior in my opinion (it will be faster for
large offsets).  last_date, last_id are the lowest values you
previously read off. It will use an index on those two columns if you
have one.  One interesting distinction is that the postgres variant
will always move forward while the oracle variant can appear to move
backwards if you are doing a non transactional scan.

Also, you can always use a cursor in either database.

merlin