Geek Answers Handbook

PostgreSQL shared cache access

I am experimenting with the EXPLAIN team and trying to figure out what a shared hit .

 Seq Scan on foo (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.030..90.500 rows=1000000 loops=1) Buffers: shared hit=512 read=7822 Total runtime: 116.080 ms

I noticed that the larger the total number of deletions, the faster the query is executed. But what is it? As far as I understood, shared read reads only physical storage, for example RAID or SSD . But why is shared hit faster? Is it stored in RAM or where?

+6
source share
1 answer

shared hit essentially means that the value has already been cached in the main memory of the computer, and there is no need to read it from the hard drive.

Access to main memory (RAM) is much faster than reading values ​​from the hard drive. And so the request is faster the more it gets.

Immediately after starting Postgres, none of the data is available in the main memory (RAM), and everything must be read from the hard drive.

Consider this step from the execution plan:

  -> Seq Scan on products.product_price (cost=0.00..3210.27 rows=392273 width=0) (actual time=0.053..103.958 rows=392273 loops=1) Output: product_id, valid_from, valid_to, price Buffers: shared read=2818 I/O Timings: read=48.382

The part “Buffers: general reading = 2818” means that 2818 blocks (every 8k) should have been read from the hard drive (and it took 48 ms - I have an SSD). These 2818 blocks were cached (“ shared buffers ”), so the next time they are needed, the database does not need to be read (again) from the (slow) hard drive.

When I re-run this statement, the plan will change to:

  -> Seq Scan on products.product_price (cost=0.00..3210.27 rows=392273 width=0) (actual time=0.012..45.690 rows=392273 loops=1) Output: product_id, valid_from, valid_to, price Buffers: shared hit=2818

This means that the 2818 blocks that the previous statement was still in the main memory (= RAM), and Postgres, did not need to be read from the hard drive.

“memory” always refers to the main memory (RAM) built into the computer and directly accessible to the CPU, and not to the “external storage”.

There are several presentations on how Postgres manages shared buffers:

+9
source

More articles:


All Articles