Hello.
I found unexpected query optimization issue: window functions can't be used as LIMIT/FETCH FIRST alternative (as far as I know, before SQL:2008 it was the only standard way to implement LIMIT in the query).
The problem is that PostgreSQL does not stop reading the records after reaching the limit specified in the WHERE clause.
This is especially unpleasant for expressions using RANK and DENSE_RANK, which can not simply be rewritten using LIMIT.
For example:
test=# SELECT version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 строка)
test=# EXPLAIN ANALYZE
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY id) n,
posts.*
FROM posts
) p
WHERE n <= 10
ORDER BY id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on p (cost=0.42..146174.41 rows=334029 width=690) (actual time=0.073..1037.148 rows=10 loops=1)
Filter: (p.n <= 10)
Rows Removed by Filter: 999990
-> WindowAgg (cost=0.42..133648.34 rows=1002086 width=690) (actual time=0.069..953.048 rows=1000000 loops=1)
-> Index Scan using posts_pkey on posts (cost=0.42..118617.05 rows=1002086 width=682) (actual time=0.059..503.496 rows=1000000 loops=1)
Planning time: 0.206 ms
Execution time: 1037.199 ms
(7 rows)
test=# EXPLAIN ANALYZE
SELECT * FROM posts
ORDER BY id
LIMIT 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..1.61 rows=10 width=682) (actual time=0.021..0.031 rows=10 loops=1)
-> Index Scan using posts_pkey on posts (cost=0.42..118617.05 rows=1002086 width=682) (actual time=0.020..0.028 rows=10 loops=1)
Planning time: 0.145 ms
Execution time: 0.065 ms
(4 rows)
test=#
--
С уважением,
Навроцкий Артем
+7 (925) 095-80-41