Does Postgres need to fetch offset tuples from the heap

user_1248361 0
Viewed 55

Let's say I have a

mytable (id, ..., insertiondate) 
index lastupdate_idx(lastupdate desc)

and I query select * from mytable order by lastupdate desc limit 10 offset 100

Will the execution run time skip offset amount of records in the index and only fetch limit amount of rows from the heap? Or does it fetch offset + limit rows from the heap and only return limit rows to the query.

Here is the plan:

Limit
   ->  Index Scan using lastupdate_idx on mytable)

I cannot tell from that (should I be able to?) and I could not find where this is addressed in the documentation.

postgresql
1 Answers

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.

user_17212390
Related