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
