Почему эти планы запросов такие разные?

У нас есть две среды RDS с репликами чтения (в тесте одна, в рабочей среде — три) и загадка: почему один и тот же запрос к тем же данным (19,5 миллионов строк для представления) + одинаковые индексы занимает около 1600 мс или более в производственная среда по сравнению с откликом тестовой среды менее миллисекунды?

Обе среды используют последнюю версию RDS PostgreSQL (11.8), используют один и тот же тип виртуальной машины 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)