我们有两个带有读取副本的RDS环境(测试有一个，生产有三个(和一个难题：为什么在生产环境中，对相同的数据(视图为1950万行(+相同的索引进行相同的查询，与测试环境的亚毫秒响应相比，耗时接近1600毫秒或更长？
这两个环境都使用最新的RDS PostgreSQL(11.8(，使用相同的VM类型db.m5.xlarge，据我所能检查，除了读取副本的数量外，配置相同。
我不知道该看什么(或研究什么(来弄清楚为什么查询计划如此不同。
编辑：测试和生产中包含单词parallel的设置都是
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
测试的查询计划是
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)
生产查询计划为
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)
如果索引排序规则为C，或者索引是使用text_pattern_ops运算符(或类似运算符(构建的，则索引只能用于支持前置LIKE查询。看起来这两件事中的一件对测试来说是正确的，但对产品来说却不是