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

Behrang Saeedzadeh, 15.02.2014 02:35:
> 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?
>
> [1] http://use-the-index-luke.com/sql/partial-results/window-functions


My local Postgres 9.3 installation does use an index for such a query.

I ran a quick (an un-scientific) test on a sample table filled with auto-generated test data:

postgres=> \d+ products
                                    Table "public.products"
      Column       |          Type          | Modifiers | Storage  | Stats target | Description
-------------------+------------------------+-----------+----------+--------------+-------------
 product_id        | integer                | not null  | plain    |              |
 ean_code          | bigint                 | not null  | plain    |              |
 product_name      | character varying(100) | not null  | extended |              |
 manufacturer_name | character varying      | not null  | extended |              |
 price             | numeric(10,2)          | not null  | main     |              |
 publish_date      | date                   | not null  | plain    |              |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
    "idx_publish_date" btree (publish_date, product_id)
Has OIDs: no


postgres=> select count(*) from products;
  count
---------
 1000000
(1 row)

Then I tried the following statement:

select *
from (
  select products.*,
         row_number() over (order by publish_date, product_id) as rn
  from products
) tmp
where rn between 200 and 300
order by publish_date, product_id;

http://explain.depesz.com/s/5u9

And Postgres does use the index idx_publish_date.

Interesting enough: my local Oracle 11.2 does *not* use an index scan for the above test (same test data).

On the other hand Oracle's table scan is much faster (about ~0.5 seconds) for the first "pages" but than gets slower
whenincreasing the limits of the pagincation.  

Oracle takes over 5 seconds when changing the limit to "between 900000 and 900100" whereas Postgres execution time
prettymuch stays the same.