0

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?

3
  • I don't think you have posted the complete execution plan for the second example, which would give you the answer. There you perform 500 individual index lookups, each fetching one page, plus 500 individual heap fetches.
    – mustaccio
    Aug 9, 2022 at 23:00
  • Reading a single row through an index lookup requires more I/O operations (=buffer reads) per row than reading a single row through a Seq Scan. I am surprised that Postgres did use an index lookup at all in the second example.
    – user1822
    Aug 10, 2022 at 5:34
  • @a_horse_with_no_name Thanks! The table was queried right after inserting so it's not been analysed yet => no stats. That's probably why index was used. See db-fiddle: db-fiddle.com/f/9ctDvNSD5zGxB7sxSYDxwS/0
    – jdziwbc
    Aug 10, 2022 at 7:06

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.