Nested Loops - Mailing list pgsql-performance

Can somebody help me avoid nested loops in below query:

--

ap_poc_db=# explain (analyze,buffers)

ap_poc_db-# select site_id, account_id FROM ap.site_exposure se

ap_poc_db-#         WHERE se.portfolio_id=-1191836

ap_poc_db-#             AND EXISTS (select 1 from ap.catevent_flood_sc_split sp where sp.migration_sourcename= 'KatRisk_SC_Flood_2015_v9' AND ST_Intersects(se.shape, sp.shape))

ap_poc_db-#             group by site_id, account_id;

                                                                                      QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Group  (cost=23479854.04..23479880.06 rows=206 width=16) (actual time=1387.825..1389.134 rows=1532 loops=1)

   Group Key: se.site_id, se.account_id

   Buffers: shared hit=172041

   ->  Gather Merge  (cost=23479854.04..23479879.04 rows=205 width=16) (actual time=1387.823..1388.676 rows=1532 loops=1)

         Workers Planned: 5

         Workers Launched: 5

         Buffers: shared hit=172041

         ->  Group  (cost=23478853.96..23478854.27 rows=41 width=16) (actual time=1346.044..1346.176 rows=255 loops=6)

               Group Key: se.site_id, se.account_id

               Buffers: shared hit=864280

               ->  Sort  (cost=23478853.96..23478854.07 rows=41 width=16) (actual time=1346.041..1346.079 rows=255 loops=6)

                     Sort Key: se.site_id, se.account_id

                     Sort Method: quicksort  Memory: 37kB

                     Buffers: shared hit=864280

                     ->  Nested Loop Semi Join  (cost=4.53..23478852.87 rows=41 width=16) (actual time=34.772..1345.489 rows=255 loops=6)

                           Buffers: shared hit=864235

                           ->  Append  (cost=0.00..156424.56 rows=123645 width=48) (actual time=1.011..204.748 rows=102990 loops=6)

                                 Buffers: shared hit=154879

                                 ->  Parallel Seq Scan on site_exposure_1191836 se  (cost=0.00..156424.56 rows=123645 width=48) (actual time=1.004..187.702 rows=102990 loops=6)

                                       Filter: (portfolio_id = '-1191836'::integer)

                                       Buffers: shared hit=154879

                           ->  Bitmap Heap Scan on catevent_flood_sc_split sp  (cost=4.53..188.54 rows=15 width=492) (actual time=0.007..0.007 rows=0 loops=617937)

                                 Recheck Cond: (se.shape && shape)

                                 Filter: ((migration_sourcename = 'KatRisk_SC_Flood_2015_v9'::bpchar) AND _st_intersects(se.shape, shape))

                                 Rows Removed by Filter: 0

                                 Heap Blocks: exact=1060

                                 Buffers: shared hit=709356

                                 ->  Bitmap Index Scan on catevent_flood_sc_split_shape_mig_src_gix  (cost=0.00..4.52 rows=45 width=0) (actual time=0.005..0.005 rows=0 loops=617937)

                                       Index Cond: (se.shape && shape)

                                       Buffers: shared hit=691115

Planning time: 116.141 ms

Execution time: 1391.785 ms

(32 rows)

ap_poc_db=#

Thank you in advance!

Regards,

Virendra