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=#