<div>Hello.</div><div> </div><div>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).</div><div> </div><div>The problem is that PostgreSQL does not stop reading the records after reaching the limit specified in the WHERE clause.</div><div> </div><div>This is especially unpleasant for expressions using RANK and DENSE_RANK, which can not simply be rewritten using LIMIT.</div><div> </div><div>For example:</div><div> </div><div><div><div><span style="font-family:andale mono,times;">test=# SELECT version();</span></div><div><span style="font-family:andale mono,times;"> version </span></div><div><span style="font-family:andale mono,times;">-----------------------------------------------------------------------------------------------------------------</span></div><div><span style="font-family:andale mono,times;"> 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</span></div><div><span style="font-family:andale mono,times;">(1 строка)</span></div><div> </div><div><span style="font-family:andale mono,times;">test=# EXPLAIN ANALYZE</span></div></div></div><div><div><div><span style="font-family:andale mono,times;">SELECT * FROM (</span></div><div><span style="font-family:andale mono,times;">SELECT</span></div><div><span style="font-family:andale mono,times;"> ROW_NUMBER() OVER (ORDER BY id) n,</span></div><div><span style="font-family:andale mono,times;"> posts.*</span></div><div><span style="font-family:andale mono,times;">FROM posts</span></div><div><span style="font-family:andale mono,times;">) p</span></div><div><span style="font-family:andale mono,times;">WHERE n <= 10</span></div><div><span style="font-family:andale mono,times;">ORDER BY id;</span></div><div><span style="font-family:andale mono,times;"> QUERY PLAN </span></div><div><span style="font-family:andale mono,times;">---------------------------------------------------------------------------------------------------------------------------------------------------</span></div><div><span style="font-family:andale mono,times;"> Subquery Scan on p (cost=0.42..146174.41 rows=334029 width=690) (actual time=0.073..1037.148 rows=10 loops=1)</span></div><div><span style="font-family:andale mono,times;"> Filter: (p.n <= 10)</span></div><div><span style="font-family:andale mono,times;"> <strong>Rows Removed by Filter: 999990</strong></span></div><div><span style="font-family:andale mono,times;"> -> WindowAgg (cost=0.42..133648.34 rows=1002086 width=690) (actual time=0.069..953.048 rows=1000000 loops=1)</span></div><div><span style="font-family:andale mono,times;"> -> 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)</span></div><div><span style="font-family:andale mono,times;"> Planning time: 0.206 ms</span></div><div><span style="font-family:andale mono,times;"> Execution time: 1037.199 ms</span></div><div><span style="font-family:andale mono,times;">(7 rows)</span></div><div> </div><div><div><div><span style="font-family:andale mono,times;">test=# EXPLAIN ANALYZE</span></div><div><span style="font-family:andale mono,times;">SELECT * FROM posts</span></div><div><span style="font-family:andale mono,times;">ORDER BY id</span></div><div><span style="font-family:andale mono,times;">LIMIT 10;</span></div><div><span style="font-family:andale mono,times;"> QUERY PLAN </span></div><div><span style="font-family:andale mono,times;">--------------------------------------------------------------------------------------------------------------------------------------</span></div><div><span style="font-family:andale mono,times;"> Limit (cost=0.42..1.61 rows=10 width=682) (actual time=0.021..0.031 rows=10 loops=1)</span></div><div><span style="font-family:andale mono,times;"> -> 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)</span></div><div><span style="font-family:andale mono,times;"> Planning time: 0.145 ms</span></div><div><span style="font-family:andale mono,times;"> Execution time: 0.065 ms</span></div><div><span style="font-family:andale mono,times;">(4 rows)</span></div><div> </div><div><span style="font-family:andale mono,times;">test=# </span></div></div></div></div></div><div> </div><div>-- </div><div>С уважением,</div><div>Навроцкий Артем</div><div>+7 (925) 095-80-41</div><div> </div>