test

Original query:

Nested Loop  (cost=1432279.17..1694731.86 rows=1 width=56) (actual time=15649.859..21418.879 rows=15 loops=1)
  ->  Merge Join  (cost=1432278.74..1694592.07 rows=1 width=64) (actual time=15649.762..21418.021 rows=15 loops=1)
        Merge Cond: (cvp.place_description_id = e.id)
        ->  Merge Join  (cost=1388528.22..1810009.47 rows=3008072 width=56) (actual time=13548.005..20774.335 rows=2864170 loops=1)
              Merge Cond: (e_1.id = cvp.place_description_id)
              ->  GroupAggregate  (cost=1388527.79..1618560.35 rows=3008072 width=40) (actual time=13547.957..18271.978 rows=2864170 loops=1)
                    Group Key: e_1.id
                    ->  Merge Join  (cost=1388527.79..1565919.09 rows=3008072 width=16) (actual time=13547.944..16844.504 rows=3026914 loops=1)
                          Merge Cond: (e_1.id = cvp_1.place_description_id)
                          ->  Index Only Scan using place_description_pkey on place_description e_1  (cost=0.43..129381.70 rows=3097706 width=8) (actual time=0.039..1526.271 rows=3038514 loops=1)
                                Heap Fetches: 3038514
                          ->  Materialize  (cost=1388521.29..1402326.50 rows=2761041 width=24) (actual time=13547.894..14358.321 rows=3026914 loops=1)
                                ->  Sort  (cost=1388521.29..1395423.89 rows=2761041 width=24) (actual time=13547.890..14053.471 rows=3026914 loops=1)
                                      Sort Key: cvp_1.place_description_id
                                      Sort Method: external merge  Disk: 100984kB
                                      ->  Hash Join  (cost=684770.09..979888.35 rows=2761041 width=24) (actual time=4968.358..12021.501 rows=3033495 loops=1)
                                            Hash Cond: (cvp_1.place_description_version_id = v_1.id)
                                            ->  Hash Join  (cost=453719.65..685378.16 rows=2761041 width=32) (actual time=3565.701..8868.438 rows=3033495 loops=1)
                                                  Hash Cond: (v_2.representative_id = cvp_1.place_description_id)
                                                  ->  Hash Join  (cost=344693.05..523620.58 rows=3052560 width=16) (actual time=2565.948..6310.931 rows=3047779 loops=1)
                                                        Hash Cond: (cvp_2.place_description_version_id = v_2.id)
                                                        ->  Hash Join  (cost=109026.60..227051.15 rows=3052560 width=16) (actual time=945.899..2957.777 rows=3047779 loops=1)
                                                              Hash Cond: (e_2.id = cvp_2.place_description_id)
                                                              ->  Seq Scan on place_description e_2  (cost=0.00..70785.06 rows=3097706 width=8) (actual time=0.006..455.271 rows=3047779 loops=1)
                                                              ->  Hash  (cost=55963.60..55963.60 rows=3052560 width=16) (actual time=944.889..944.889 rows=3047779 loops=1)
                                                                    Buckets: 131072  Batches: 64  Memory Usage: 3261kB
                                                                    ->  Seq Scan on place_description_current_version_pointer cvp_2  (cost=0.00..55963.60 rows=3052560 width=16) (actual time=0.005..419.413 rows=3047779 loops=1)
                                                        ->  Hash  (cost=153510.42..153510.42 rows=4726242 width=16) (actual time=1612.298..1612.298 rows=4726242 loops=1)
                                                              Buckets: 131072  Batches: 128  Memory Usage: 2733kB
                                                              ->  Seq Scan on place_description_version v_2  (cost=0.00..153510.42 rows=4726242 width=16) (actual time=0.006..872.889 rows=4726242 loops=1)
                                                  ->  Hash  (cost=55963.60..55963.60 rows=3052560 width=16) (actual time=998.506..998.506 rows=3047779 loops=1)
                                                        Buckets: 131072  Batches: 64  Memory Usage: 3272kB
                                                        ->  Seq Scan on place_description_current_version_pointer cvp_1  (cost=0.00..55963.60 rows=3052560 width=16) (actual time=0.007..449.652 rows=3047779 loops=1)
                                            ->  Hash  (cost=153510.42..153510.42 rows=4726242 width=8) (actual time=1402.214..1402.214 rows=4726242 loops=1)
                                                  Buckets: 131072  Batches: 64  Memory Usage: 3913kB
                                                  ->  Seq Scan on place_description_version v_1  (cost=0.00..153510.42 rows=4726242 width=8) (actual time=0.004..687.520 rows=4726242 loops=1)
              ->  Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp  (cost=0.43..116136.10 rows=3052560 width=16) (actual time=0.043..1607.148 rows=3038513 loops=1)
        ->  Materialize  (cost=43750.53..43758.57 rows=1 width=32) (actual time=470.582..470.744 rows=15 loops=1)
              ->  Nested Loop  (cost=43750.53..43758.56 rows=1 width=32) (actual time=470.574..470.726 rows=15 loops=1)
                    ->  Limit  (cost=43750.10..43750.11 rows=1 width=8) (actual time=470.549..470.591 rows=15 loops=1)
                          ->  Group  (cost=43750.10..43750.11 rows=1 width=8) (actual time=470.547..470.577 rows=15 loops=1)
                                Group Key: v_3.representative_id
                                ->  Sort  (cost=43750.10..43750.10 rows=2 width=8) (actual time=470.546..470.559 rows=15 loops=1)
                                      Sort Key: v_3.representative_id
                                      Sort Method: quicksort  Memory: 25kB
                                      ->  Gather  (cost=43749.88..43750.09 rows=2 width=8) (actual time=466.501..470.630 rows=15 loops=1)
                                            Workers Planned: 2
                                            Workers Launched: 2
                                            ->  Group  (cost=42749.88..42749.89 rows=1 width=8) (actual time=457.188..457.192 rows=5 loops=3)
                                                  Group Key: v_3.representative_id
                                                  ->  Sort  (cost=42749.88..42749.88 rows=1 width=8) (actual time=457.187..457.187 rows=5 loops=3)
                                                        Sort Key: v_3.representative_id
                                                        Sort Method: quicksort  Memory: 25kB
                                                        ->  Nested Loop  (cost=1197.84..42749.87 rows=1 width=8) (actual time=236.004..455.781 rows=5 loops=3)
                                                              ->  Nested Loop  (cost=1197.41..42741.48 rows=1 width=16) (actual time=235.982..455.714 rows=5 loops=3)
                                                                    ->  Hash Join  (cost=1196.98..42733.08 rows=1 width=24) (actual time=235.950..455.635 rows=5 loops=3)
                                                                          Hash Cond: (e_3.data_source_id = ds.id)
                                                                          ->  Nested Loop  (cost=1188.80..42724.69 rows=79 width=32) (actual time=174.777..455.569 rows=6 loops=3)
                                                                                ->  Hash Join  (cost=1188.37..42684.10 rows=79 width=32) (actual time=174.752..455.454 rows=6 loops=3)
                                                                                      Hash Cond: (cvp_3.place_description_version_id = v_3.id)
                                                                                      ->  Parallel Seq Scan on place_description_current_version_pointer cvp_3  (cost=0.00..38157.00 rows=1271900 width=16) (actual time=0.006..205.320 rows=1015926 loops=3)
                                                                                      ->  Hash  (cost=1184.72..1184.72 rows=292 width=32) (actual time=14.024..14.024 rows=21 loops=3)
                                                                                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                                                            ->  Bitmap Heap Scan on place_description_version v_3  (cost=58.26..1184.72 rows=292 width=32) (actual time=13.942..14.015 rows=21 loops=3)
                                                                                                  Recheck Cond: ((name)::text ~~* 'pias%'::text)
                                                                                                  Rows Removed by Index Recheck: 5
                                                                                                  Filter: (confidence >= '0'::numeric)
                                                                                                  Heap Blocks: exact=26
                                                                                                  ->  Bitmap Index Scan on place_description_version_trigram_ix5  (cost=0.00..58.19 rows=292 width=0) (actual time=13.912..13.912 rows=26 loops=3)
                                                                                                        Index Cond: ((name)::text ~~* 'pias%'::text)
                                                                                ->  Index Scan using place_description_pkey on place_description e_3  (cost=0.43..0.51 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=18)
                                                                                      Index Cond: (id = cvp_3.place_description_id)
                                                                          ->  Hash  (cost=8.17..8.17 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=3)
                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                                ->  Index Scan using data_source_ux1 on data_source ds  (cost=0.15..8.17 rows=1 width=8) (actual time=0.015..0.019 rows=1 loops=3)
                                                                                      Index Cond: ((name)::text = 'SAFEGRAPH'::text)
                                                                    ->  Index Only Scan using address_pkey on address a  (cost=0.43..8.39 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=15)
                                                                          Index Cond: (id = v_3.address_id)
                                                                          Heap Fetches: 5
                                                              ->  Index Only Scan using geocode_pkey on geocode g  (cost=0.43..8.39 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=15)
                                                                    Index Cond: (id = v_3.geocode_id)
                                                                    Heap Fetches: 5
                    ->  Index Scan using place_description_pkey on place_description e  (cost=0.43..8.45 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=15)
                          Index Cond: (id = v_3.representative_id)
  ->  Index Only Scan using place_description_version_pkey on place_description_version v  (cost=0.43..0.62 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=15)
        Index Cond: (id = cvp.place_description_version_id)
        Heap Fetches: 15
  SubPlan 1
    ->  Aggregate  (cost=139.16..139.17 rows=1 width=32) (actual time=0.038..0.039 rows=1 loops=15)
          ->  Nested Loop  (cost=1.29..139.11 rows=10 width=24) (actual time=0.024..0.025 rows=1 loops=15)
                ->  Nested Loop  (cost=0.86..132.95 rows=10 width=32) (actual time=0.017..0.018 rows=1 loops=15)
                      ->  Index Scan using place_description_pkey on place_description e_4  (cost=0.43..48.48 rows=10 width=24) (actual time=0.006..0.006 rows=1 loops=15)
                            Index Cond: (id = ANY ((array_agg(e_2.id))))
                      ->  Index Scan using place_description_current_version_pointer_ux1 on place_description_current_version_pointer cvp_4  (cost=0.43..8.45 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=15)
                            Index Cond: (place_description_id = e_4.id)
                ->  Index Only Scan using place_description_version_pkey on place_description_version v_4  (cost=0.43..0.62 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=15)
                      Index Cond: (id = cvp_4.place_description_version_id)
                      Heap Fetches: 15
Planning time: 4.156 ms
Execution time: 21437.470 ms