PostgreSQL size of used shared buffer vs actual result size

Question

I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading so much from buffers it often performs I/O which is very slow. I measured the actual returned data size and it's always ~100 times smaller no matter how big the joined sets are. I am wondering why the difference between read buffers and actual data size is so huge when querying using index.

I reproduced the issue using trivial example (db-fiddle). Let's create a simple user table and insert 500 rows:

CREATE TABLE users(id int primary key, user_data text);
INSERT INTO users(id, user_data) select generate_series(1,500), random()::text;
select pg_size_pretty(pg_relation_size('users'));

After inserting 500 rows actual table size is 32 kB. Lets query all rows and check buffers used:

explain (analyze, buffers) select * from users;

Seq Scan on users  (cost=0.00..9.00 rows=500 width=24) (actual time=0.032..5.150 rows=500 loops=1)
  Buffers: shared hit=4
Planning time: 1.546 ms
Execution time: 10.484 ms

Selecting all rows yields Buffers: shared hit=4. 4*8kB = 32kB which is exactly table size. Great.

If I however query all rows using id condition:

explain (analyze, buffers) select * from users where id in (1, 2, 3, .... , 499, 500);

Index Scan using users_pkey on users  (cost=0.27..165.16 rows=316 width=24) (actual time=0.044..5.704 rows=500 loops=1)
  Index Cond: (id = ANY ('{1,2,3,...,500}'::integer[]))
  Buffers: shared hit=1006
Planning time: 0.521 ms
Execution time: 10.424 ms

We get Buffers: shared hit=1006. 1006 * 8kB = 8048kB is way higher compared to previous 32kB.

Where is this difference coming from?

Answer 1

You should show the real plans of real queries. Or at realistic in size and performance, if you don't want to use actual real data. What you have here does no IO at all. All the buffer accesses are hits, zero reads. It is hitting the same buffers, already in memory, over and over again, as it constantly steps between the index and the table. The EXPLAIN (...BUFFERS) buffer accounting method makes no attempt to deduplicate buffer access counts. If the buffer is unpinned and then re-pinned, then it counts as another access.

Anything you learn from this toy example is not likely to meaningfully transfer over to the real situation.

Also, 9.6 is end-of-life, and whatever you learn about it from your toy example is somewhat dubious if transferred to a modern version.