Adjust settings to reduce the number of read blocks for large query in postgres

Question

Using postgres 9.6, i don't understand how shared buffers work with indexes.

Setup

• Postgres 9.6
• All default settings
  • shared_buffers: 128Mb
  • work_mem: 4Mb
  • block_size: 8192
  • ...

That means that shared_buffer size is 128 * 1024 * 1024 / 8192 = 16384 blocks.

Test data

I create a simple table with random data and on index on each column.

DROP TABLE IF EXISTS sandbox;


CREATE TABLE sandbox AS
SELECT generate_series(1, 4000000) AS pk,
       random() AS x;

CREATE INDEX ON sandbox(pk);
CREATE INDEX ON sandbox(x);

I went with 4M rows. Here's the size taken by table & index:

postgres=# SELECT relname AS "relation",
       pg_relation_size(C.oid) / 8192 AS "blocks",
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE relname = 'sandbox' ;
 relation | blocks |  size  
----------+--------+--------
 sandbox  |  21622 | 169 MB

So the table size (169 Mb) is greater than the shared buffer (128Mb)

Explain & analyze

I want to retrieve the rows with a condition on x. This will extract ~2% of my data.

postgres=# explain (ANALYZE, buffers, format text)
SELECT *
FROM sandbox
WHERE x < .02;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sandbox  (cost=24961.76..63250.42 rows=1333333 width=12) (actual time=29.059..347.203 rows=80218 loops=1)
   Recheck Cond: (x < '0.02'::double precision)
   Heap Blocks: exact=21113
   Buffers: shared read=21335
   ->  Bitmap Index Scan on sandbox_x_idx  (cost=0.00..24628.43 rows=1333333 width=0) (actual time=20.458..20.458 rows=80218 loops=1)
         Index Cond: (x < '0.02'::double precision)
         Buffers: shared read=222
 Planning time: 0.293 ms
 Execution time: 573.949 ms

One can see that:

• The Bitmap Index Scan on sandbox_x_idx indicate
  • rows=1333333 for the cost part. So 1/3 of the table size. What is the meaning of that figure?
  • rows=80218 which represents 2% of the table size.
• Heap blocks is equal to 21113. This is approximately 164Mb so this it stores in the heap all the table right? Why does he have to go through all the table despite the index?
• Buffers is not used as there are only read (from disk) and no hits (from memory). Even if i rerun the query, i get the same behaviour.

Now i'm running a query on pk and i still fetch ~2% of the data:

postgres=# explain (ANALYZE, buffers, format text)
SELECT *
FROM sandbox
WHERE pk < 80000;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sandbox  (cost=24961.76..63250.42 rows=1333333 width=12) (actual time=12.901..242.663 rows=79999 loops=1)
   Recheck Cond: (pk < 80000)
   Heap Blocks: exact=433
   Buffers: shared hit=654
   ->  Bitmap Index Scan on sandbox_pk_idx  (cost=0.00..24628.43 rows=1333333 width=0) (actual time=12.770..12.770 rows=79999 loops=1)
         Index Cond: (pk < 80000)
         Buffers: shared hit=221
 Planning time: 0.082 ms
 Execution time: 466.469 ms

One can note: - heap blocks is much smaller: 433 which represents 2% of the total blocks above (21k) - Those blocks are put in the buffer since we see hit

My understanding so far is:

• The block repartition follows the pk. so the first block contains the pk from 1 to 200, the second contains pk from 201 to 400 and so on
• The block repartition is totally orthogonal to x. In each block there is a row with x < 0.02
• So a query with a condition on x will have to read all the blocks despite the index. Since there are more blocks that what shared_buffer can handle, each block go in and out the shared buffer at each query.

Is this correct?

I'm interested in selecting rows with a condition on x and still benefits from the shared buffers. That means i need the blocks to depends somehow on x. How can i do that?

• Should i lower the block_size?
• Should i use partitioning?

Other ideas?

Answer 1

I agree with @dezso you should either start partitioning, or maybe have a look at the CLUSTER instruction (https://www.postgresql.org/docs/9.4/static/sql-cluster.html). As described, it will physically reorder data based on the index information which seems to be what you're trying to achieve.

Answer 2

rows=1333333 for the cost part. So 1/3 of the table size. What is the meaning of that figure?

That means you haven't yet run ANALYZE sandbox (or waited for autovacuum to do it for you) so that PostgreSQL doesn't have an accurate estimate of the number of rows which will be returned. It just assumes it will return 1/3 of the table, which is the default assumption for inequality conditions when there are no statistics.

Heap blocks is equal to 21113. This is approximately 164Mb so this it stores in the heap all the table right? Why does he have to go through all the table despite the index?

You are selecting all columns (*) from the table. The index doesn't store all the columns, just the ones included in the index. It has to go to the table to get the rest.

I'm interested in selecting rows with a condition on x and still benefits from the shared buffers.

First, don't get too hung up on shared buffers hit ratios. PostgreSQL makes extensive use of the operating system's file cache, so the blocks are probably still cached somewhere, even if not in shared_buffers. You should probably turn on track_io_timing; that will give you info on how long it took to read the blocks, which is more important than where it happened to find them.

You can cluster the table on the index on x (CLUSTER sandbox USING sandbox_x_idx), which will put all the rows with similar x into nearby blocks. But if you still can, it might be better to populate the table in order of x in the first place. That way pk will get assigned in that order, and so both of your indexes can be well-clustered simultaneously. If it is too late to do that, then you will have to pick which index is more important to be clustered on.