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 when increasing the limits of the pagincation.

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