James McPherson
Reputation: 2576
Why are these query plans so different?
We have two RDS environments with read replicas (test has one, production has three) and a conundrum: why does the same query, over the same data (19.5million rows for the view) + same indexes, take close to 1600msec or more in the production environment compared to the test environment's sub-millisecond response?
Both environments are using the latest RDS PostgreSQL (11.8), are using the same VM type of db.m5.xlarge, and to the best of my ability to check, are configured the same - apart from the number of read replicas.
I don't know what to look at (or into) to figure out why the query plans are so different.
Edit: The settings in both test and production containing the word parallel are
enable_parallel_append on
enable_parallel_hash on
force_parallel_mode off
max_parallel_maintenance_workers 2
max_parallel_workers 8
max_parallel_workers_per_gather 2
min_parallel_index_scan_size 512kB
min_parallel_table_scan_size 8MB
parallel_leader_participation on
parallel_setup_cost 1000
parallel_tuple_cost 0.1
The query plan for test is
db=> explain (analyze, buffers)
select columns
from view
where ( search_column like '342 KING ST C'||'%' ESCAPE '~' OR search_column like '342 KING STREET C' ||'%' ESCAPE '~' OR search_column like '342 KING SAINT C' ||'%' ESCAPE '~' )
AND result_type in (1, 2, 3, 4)
limit 10
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=18.24..97.45 rows=10 width=152) (actual time=0.034..0.095 rows=9 loops=1)
Buffers: shared hit=76
-> Append (cost=18.24..44543.84 rows=5621 width=152) (actual time=0.033..0.093 rows=9 loops=1)
Buffers: shared hit=76
-> Subquery Scan on "*SELECT* 1" (cost=18.24..44464.47 rows=5449 width=151) (actual time=0.033..0.075 rows=9 loops=1)
Buffers: shared hit=55
-> Nested Loop Left Join (cost=18.24..44409.98 rows=5449 width=219) (actual time=0.032..0.073 rows=9 loops=1)
Buffers: shared hit=55
-> Bitmap Heap Scan on _address_full_location a (cost=17.80..21.82 rows=5449 width=110) (actual time=0.022..0.031 rows=9 loops=1)
Recheck Cond: ((address_display ~~ '342 KING ST C%'::text) OR (address_display ~~ '342 KING STREET C%'::text) OR (address_display ~~ '342 KING SAINT C%'::text))
Filter: ((address_display ~~ '342 KING ST C%'::text) OR (address_display ~~ '342 KING STREET C%'::text) OR (address_display ~~ '342 KING SAINT C%'::text))
Heap Blocks: exact=7
Buffers: shared hit=19
-> BitmapOr (cost=17.80..17.80 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=12
-> Bitmap Index Scan on _address_full_location_ix_address_search_isunit (cost=0.00..4.57 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((address_display >= '342 KING ST C'::text) AND (address_display < '342 KING ST D'::text))
Buffers: shared hit=4
-> Bitmap Index Scan on _address_full_location_ix_address_search_isunit (cost=0.00..4.57 rows=1 width=0) (actual time=0.006..0.006 rows=9 loops=1)
Index Cond: ((address_display >= '342 KING STREET C'::text) AND (address_display < '342 KING STREET D'::text))
Buffers: shared hit=4
-> Bitmap Index Scan on _address_full_location_ix_address_search_isunit (cost=0.00..4.57 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((address_display >= '342 KING SAINT C'::text) AND (address_display < '342 KING SAINT D'::text))
Buffers: shared hit=4
-> Index Scan using _property_ix_property_id on _property p (cost=0.43..8.14 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=9)
Index Cond: (a.property_id = property_id)
Buffers: shared hit=36
-> Bitmap Heap Scan on _address_street_location st (cost=13.43..17.45 rows=166 width=172) (actual time=0.006..0.006 rows=0 loops=1)
Recheck Cond: ((street_display ~~ '342 KING ST C%'::text) OR (street_display ~~ '342 KING STREET C%'::text) OR (street_display ~~ '342 KING SAINT C%'::text))
Filter: ((street_display ~~ '342 KING ST C%'::text) OR (street_display ~~ '342 KING STREET C%'::text) OR (street_display ~~ '342 KING SAINT C%'::text))
Buffers: shared hit=9
-> BitmapOr (cost=13.43..13.43 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
Buffers: shared hit=9
-> Bitmap Index Scan on _address_street_location_ix_street_search (cost=0.00..4.43 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((street_display >= '342 KING ST C'::text) AND (street_display < '342 KING ST D'::text))
Buffers: shared hit=3
-> Bitmap Index Scan on _address_street_location_ix_street_search (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.002 rows=0 loops=1)
Index Cond: ((street_display >= '342 KING STREET C'::text) AND (street_display < '342 KING STREET D'::text))
Buffers: shared hit=3
-> Bitmap Index Scan on _address_street_location_ix_street_search (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.002 rows=0 loops=1)
Index Cond: ((street_display >= '342 KING SAINT C'::text) AND (street_display < '342 KING SAINT D'::text))
Buffers: shared hit=3
-> Bitmap Heap Scan on _address_suburb_location su (cost=12.90..16.91 rows=5 width=158) (actual time=0.004..0.004 rows=0 loops=1)
Recheck Cond: ((suburb_display ~~ '342 KING ST C%'::text) OR (suburb_display ~~ '342 KING STREET C%'::text) OR (suburb_display ~~ '342 KING SAINT C%'::text))
Filter: ((suburb_display ~~ '342 KING ST C%'::text) OR (suburb_display ~~ '342 KING STREET C%'::text) OR (suburb_display ~~ '342 KING SAINT C%'::text))
Buffers: shared hit=6
-> BitmapOr (cost=12.90..12.90 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
Buffers: shared hit=6
-> Bitmap Index Scan on _address_suburb_location_ix_suburb_search (cost=0.00..4.30 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((suburb_display >= '342 KING ST C'::text) AND (suburb_display < '342 KING ST D'::text))
Buffers: shared hit=2
-> Bitmap Index Scan on _address_suburb_location_ix_suburb_search (cost=0.00..4.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((suburb_display >= '342 KING STREET C'::text) AND (suburb_display < '342 KING STREET D'::text))
Buffers: shared hit=2
-> Bitmap Index Scan on _address_suburb_location_ix_suburb_search (cost=0.00..4.30 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((suburb_display >= '342 KING SAINT C'::text) AND (suburb_display < '342 KING SAINT D'::text))
Buffers: shared hit=2
-> Bitmap Heap Scan on _address_postcode_location pc (cost=12.88..16.90 rows=1 width=147) (actual time=0.006..0.006 rows=0 loops=1)
Recheck Cond: ((postcode_display ~~ '342 KING ST C%'::text) OR (postcode_display ~~ '342 KING STREET C%'::text) OR (postcode_display ~~ '342 KING SAINT C%'::text))
Filter: ((postcode_display ~~ '342 KING ST C%'::text) OR (postcode_display ~~ '342 KING STREET C%'::text) OR (postcode_display ~~ '342 KING SAINT C%'::text))
Buffers: shared hit=6
-> BitmapOr (cost=12.88..12.88 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
Buffers: shared hit=6
-> Bitmap Index Scan on _address_postcode_location_ix_postcode_search (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((postcode_display >= '342 KING ST C'::text) AND (postcode_display < '342 KING ST D'::text))
Buffers: shared hit=2
-> Bitmap Index Scan on _address_postcode_location_ix_postcode_search (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((postcode_display >= '342 KING STREET C'::text) AND (postcode_display < '342 KING STREET D'::text))
Buffers: shared hit=2
-> Bitmap Index Scan on _address_postcode_location_ix_postcode_search (cost=0.00..4.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ((postcode_display >= '342 KING SAINT C'::text) AND (postcode_display < '342 KING SAINT D'::text))
Buffers: shared hit=2
Planning Time: 3.427 ms
Execution Time: 0.174 ms
(74 rows)
The production query plan is
db=> explain (analyze, buffers)
select columns
from view
where ( search_column like '342 KING ST C'||'%' ESCAPE '~' OR search_column like '342 KING STREET C' ||'%' ESCAPE '~' OR search_column like '342 KING SAINT C' ||'%' ESCAPE '~' )
AND result_type in (1, 2, 3, 4)
limit 10
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1000.00..2053.45 rows=10 width=151) (actual time=1243.140..1680.998 rows=9 loops=1)
Buffers: shared hit=15815 read=425564
I/O Timings: read=835.908
-> Gather (cost=1000.00..603046.39 rows=5715 width=151) (actual time=1243.139..1682.255 rows=9 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=15815 read=425564
I/O Timings: read=835.908
-> Parallel Append (cost=0.00..601474.89 rows=5614 width=151) (actual time=1329.613..1677.727 rows=3 loops=3)
Buffers: shared hit=15815 read=425564
I/O Timings: read=835.908
-> Subquery Scan on "*SELECT* 1" (cost=0.43..581643.66 rows=5541 width=150) (actual time=1290.180..1638.292 rows=3 loops=3)
Buffers: shared hit=328 read=425564
I/O Timings: read=835.908
-> Nested Loop Left Join (cost=0.43..581588.25 rows=2309 width=218) (actual time=1290.178..1638.288 rows=3 loops=3)
Buffers: shared hit=328 read=425564
I/O Timings: read=835.908
-> Parallel Seq Scan on _address_full_location a (cost=0.00..562773.42 rows=2309 width=109) (actual time=1290.140..1638.218 rows=3 loops=3)
Filter: ((address_display ~~ '342 KING ST C%'::text) OR (address_display ~~ '342 KING STREET C%'::text) OR (address_display ~~ '342 KING SAINT C%'::text))
Rows Removed by Filter: 6258793
Buffers: shared hit=290 read=425564
I/O Timings: read=835.908
-> Index Scan using _property_ix_property_id on _property p (cost=0.43..8.14 rows=1 width=5) (actual time=0.015..0.016 rows=1 loops=9)
Index Cond: (a.property_id = property_id)
Buffers: shared hit=38
-> Parallel Seq Scan on _address_street_location st (cost=0.00..19162.97 rows=70 width=172) (actual time=57.433..57.433 rows=0 loops=2)
Filter: ((street_display ~~ '342 KING ST C%'::text) OR (street_display ~~ '342 KING STREET C%'::text) OR (street_display ~~ '342 KING SAINT C%'::text))
Rows Removed by Filter: 280386
Buffers: shared hit=15074
-> Parallel Seq Scan on _address_suburb_location su (cost=0.00..535.86 rows=3 width=158) (actual time=2.798..2.798 rows=0 loops=1)
Filter: ((suburb_display ~~ '342 KING ST C%'::text) OR (suburb_display ~~ '342 KING STREET C%'::text) OR (suburb_display ~~ '342 KING SAINT C%'::text))
Rows Removed by Filter: 17472
Buffers: shared hit=356
-> Parallel Seq Scan on _address_postcode_location pc (cost=0.00..104.33 rows=1 width=147) (actual time=0.629..0.629 rows=0 loops=1)
Filter: ((postcode_display ~~ '342 KING ST C%'::text) OR (postcode_display ~~ '342 KING STREET C%'::text) OR (postcode_display ~~ '342 KING SAINT C%'::text))
Rows Removed by Filter: 4598
Buffers: shared hit=57
Planning Time: 2.846 ms
Execution Time: 1682.402 ms
(39 rows)
Upvotes: 0
Views: 151
Answers (1)
jjanes
Reputation: 44373
An index can only be used to support a front-anchored LIKE query if the index collation is C, or the index is built with the text_pattern_ops operator (or similar). It looks like one of those two things is true for test, but is not true for prod.
Upvotes: 3
Related Questions
- AWS RDS query returning inconsistent results
- Understanding why my Postgres queries are faster without indexes
- Very different query plans for almost identical queries
- Postgresql Query 10x Slower After Migration from EC2 to RDS
- Amazon RDS - Postgres not using index for SELECT queries
- Inconsistent response from Postgres DB on similar queries
- Why would there be varying response times for the same query?
- Postgres uses wrong index in query plan
- Postgresql query plan discrepancy
- Why so different query plans?