I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora.
Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain cached pages.
I have a table with an index and when the planner uses the index the query is substantially slower (spending more time on IO from what I can tell). However, when I drop an index the query is faster, it uses a parallel sequential scan.
The Schema of the table:
CREATE TABLE max_test_2018_3
(
timestamp_ timestamp,
person_alias varchar(128),
visitor_id varchar(128),
session_id varchar(24),
page_title varchar(1024),
location_host varchar(256),
location_path varchar(1024),
location_query varchar(1024),
location_fragment varchar(1024),
referrer_host varchar(256),
referrer_path varchar(1024),
referrer_query varchar(1024),
referrer_fragment varchar(1024),
duration integer,
location_secure boolean,
referrer_secure boolean,
id uuid,
person_id uuid,
custom_properties jsonb
);
The index DDL is:
CREATE INDEX max_test_2018_3_ix ON max_test_2018_3
USING btree ( location_path varchar_pattern_ops);
And the query is:
explain (analyze, buffers)
SELECT O.person_id, 1 as step1, min(timestamp_) AS timestamp_
FROM max_test_2018_3 O
WHERE O.location_path like '/tax-calculator%'
GROUP BY O.person_id
Check the following two explains - note that both explains show minimal or no buffer hits. One shows the execution with the index and the other shows execution after the index has been dropped:
No index -> https://explain.depesz.com/s/N4uo With index -> https://explain.depesz.com/s/69WS
The plan that includes the index scan takes 193 seconds vs 43 seconds for a sequential scan.
Why is step 2 in the "With-index" plan so slow? It reads in only 118372 buffers (about 118 MB of data), yet this step took 193 seconds. Why? When the buffer cache is warmed up the same with-index query takes a couple seconds. So we must concede that the IO is responsible for this extra time.
I've run this experiment several times on multiple similar datasets. Here is another example that demonstrates index scan operations that require IO and do not benefit from buffer cache.
No index -> https://explain.depesz.com/s/g6JU with Index ->https://explain.depesz.com/s/Sw6Q
Again, you can see that the index scan is unreasonably slow. The planner attempts to use the index because it believes it will speed up the query, yet such queries are consistently slower.
With regard to parameters that affecting the planner I've set the following two:
random_page_cost = 1
seq_page_cost = 1
The rationale being that for an SSD drive the random_page_cost
does not require seeking and so can be set to the same value as seq_page_cost
.
The table is vacuumed with statistics up-to date. The table max_test_2018_3
contains approximately 18 million rows and is about 9GB on disk. The above index on that table is 440 MB.
Why do the plans that use an index scan perform so poorly?
I/O Timing enabled Plans
- with index https://explain.depesz.com/s/84kE no
- no index(parallel scan) https://explain.depesz.com/s/CRBp
Tweeking parallelism settings
The explains above have default parameter settings with respect to parallelism. The only two exceptions are:
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
As per the suggestions, I've experimented with with the following settings:
parallel_setup_cost = 10
parallel_tuple_cost = 0.001
Defaults for these are 1000 and 0.1 respectively. These changes did not affect the plans and PostgreSQL still chose to use the index even though a parallel sequential scan is faster.
The index size is 440MB, far larger than the default min_parallel_index_scan_size of 512kB
track_io_timing = on
?EXPLAIN (ANALYZE, BUFFERS)
. It should contain I/O statistics too.