Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.













-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux