3

The query planner in Postgres10 seems to be ignoring the fact that my tables are clustered. I have to explicitly put "hints" in my query to have it perform the correct type of join.

Here's my situation...

I have 3 tables: l_str, l_dbl, and l_data. The tables are first created, then populated, then clustered, then vacuum/analyzed. After that, they're only ever read from (never written to).

-- Create tables
CREATE TABLE L_STR(
  KEY                  VARCHAR(255)        NOT NULL,
  NAME                 VARCHAR(255)        NOT NULL,
  VAL                  VARCHAR(255),
  PRIMARY KEY(KEY, NAME)
);
CREATE TABLE L_DBL(
  KEY                  VARCHAR(255)        NOT NULL,
  NAME                 VARCHAR(255)        NOT NULL,
  VAL                  DOUBLE PRECISION,
  PRIMARY KEY(KEY, NAME)
);
CREATE TABLE L_DATA(
  KEY                   VARCHAR(255)        NOT NULL,
  DATA_BLOCK            BYTEA               NOT NULL,
  PRIMARY KEY(KEY)
);

-- Insert rows
...

-- Cluster tables and run analyze
CREATE INDEX DATA_IDX ON L_DATA(KEY);
CLUSTER L_DATA USING DATA_IDX;
VACUUM ANALYZE L_DATA;

CREATE INDEX STR_IDX ON L_STR(NAME, VAL, KEY);
CLUSTER L_STR USING STR_IDX;
VACUUM ANALYZE L_STR;

CREATE INDEX DBL_IDX ON L_DBL(NAME, VAL, KEY); 
CLUSTER L_DBL USING DBL_IDX;
VACUUM ANALYZE L_DBL;

pg_stats for my tables after this process...

SELECT tablename, attname, correlation FROM ORDER BY 1, 2; 
| tablename |  attname   | correlation  |
|-----------|------------|--------------|
| l_data    | data_block | -0.011323823 |
| l_data    | key        |          1.0 |
| l_dbl     | name       |          1.0 |
| l_dbl     | key        |  -0.10221779 |
| l_dbl     | val        |   0.72073925 |
| l_str     | name       |          1.0 |
| l_str     | key        |  0.038144425 |
| l_str     | val        |  -0.27145308 |

Given that my tables are clustered, I would expect the query planner to take that information into account when choosing what type of join to do. Take the following example...

EXPLAIN (analyze true, buffers true, timing true)
SELECT x0.* FROM
  l_data x0
    INNER JOIN
  (SELECT KEY FROM l_str WHERE name='data_type' and val='metatranscriptomics') x1
    ON x0.KEY = x1.KEY; 
Nested Loop  (cost=0.70..130.30 rows=69 width=821) (actual time=0.235..6.210 rows=835 loops=1)
  Buffers: shared hit=2512 read=11
  ->  Index Only Scan using str_idx on l_str  (cost=0.42..2.90 rows=69 width=22) (actual time=0.212..0.568 rows=835 loops=1)
        Index Cond: ((name = 'data_type'::text) AND (val = 'metatranscriptomics'::text))
        Heap Fetches: 0
        Buffers: shared hit=1 read=11
  ->  Index Scan using data_idx on l_data x0  (cost=0.28..1.85 rows=1 width=821) (actual time=0.006..0.006 rows=1 loops=835)
        Index Cond: ((key)::text = (l_str.key)::text)
        Buffers: shared hit=2511
Planning time: 1.073 ms
Execution time: 6.271 ms

Given the clustering I've applied to my tables, I expected the above query to be performed using a merge join instead of a nested loop. The first input into the inner join (table l_data) is ordered by KEY. The second input into the inner join should also be ordered by KEY (table l_str is clustered on name,val,key).

I was able to get the query planner to perform a merge join by shoving an "ORDER BY" for l_data...

EXPLAIN (analyze true, buffers true, timing true)
   SELECT x0.* FROM
     (SELECT * FROM public.l_data ORDER BY KEY) x0
       INNER JOIN
     (SELECT KEY FROM public.l_str WHERE name='data_type' and val='metatranscriptomics') x1
       ON x0.KEY = x1.KEY;
Merge Join  (cost=0.84..805.26 rows=69 width=821) (actual time=0.395..4.124 rows=835 loops=1)
  Merge Cond: ((l_data.key)::text = (str_idx.key)::text)
  Buffers: shared hit=657
  ->  Index Scan using data_idx on l_data  (cost=0.28..732.48 rows=5533 width=821) (actual time=0.005..1.405 rows=5533 loops=1)
        Buffers: shared hit=645
  ->  Index Only Scan using l_str on str_idx  (cost=0.42..2.90 rows=69 width=22) (actual time=0.021..0.179 rows=835 loops=1)
        Index Cond: ((name = 'data_type'::text) AND (val = 'metatranscriptomics'::text))
        Heap Fetches: 0
        Buffers: shared hit=12
Planning time: 0.589 ms
Execution time: 4.161 ms

It doesn't really make sense to do this because l_data's should already be physically ordered by KEY. Either way, you can see how much better the query performs: 657 vs 2512 hits / 4.1ms vs 6.2ms.

Is there anyway to get Postgres's query planner to take physical table ordering into account (e.g. query hints, config options, etc..)?

Just in case it matters: I'm using Postgres 10.5 on a 4 core Windows10 machine with a SSD.

Here's my configuration:

max_connections = 20
shared_buffers = 512MB
effective_cache_size = 4608MB
maintenance_work_mem = 768MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
work_mem = 24029kB
min_wal_size = 4GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
1
  • Trying to optimize a query that runs under 100ms makes no sense. Optimization is a delicate process and the first rule of optimization is : don't. (The second rule is : don't now.) My advice would be to optimize what's need to be optimized and a 6 (or 4) ms query don't need optimization. But maybe your real problem is on a production server when that query is much slower ? – Arkhena Dec 18 '18 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.