Does Postgres need to fetch offset tuples from the heap

Solution 1:

It fetches them all from the heap. After setting abalance to a random value and indexing it, I restarted PostgreSQL to clear the shared_buffers, and did this (with 2,000,000 tuples):

explain (analyze , buffers ) select * from pgbench_accounts order by abalance desc limit 10 offset 100;

getting:

QUERY PLAN                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=9.90..10.85 rows=10 width=97) (actual time=15.247..15.384 rows=10 loops=1)
   Buffers: shared hit=1 read=114 dirtied=1
   I/O Timings: read=15.088
   ->  Index Scan Backward using pgbench_accounts_abalance_idx on pgbench_accounts  (cost=0.43..189448.93 rows=2000000 width=97) (actual time=0.113..15.363 rows=110 loops=1)
         Buffers: shared hit=1 read=114 dirtied=1
         I/O Timings: read=15.088
 Planning:
   Buffers: shared hit=44 read=4 dirtied=1
   I/O Timings: read=0.173
 Planning Time: 4.414 ms
 Execution Time: 15.493 ms

The 114 buffers it had to read can only be explained as 110 heap tuples, plus a few pages for the index.

Does Postgres need to fetch offset tuples from the heap

Solution 1:

Related

Recent Posts