Kumar, Virendra wrote: > 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 > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [...] > 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 [...] > -> 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
With a join condition like that (using on a function result), only a nested loop join is possible. I don't know how selective sp.migration_sourcename= 'KatRisk_SC_Flood_2015_v9' is; perhaps an index on the column can help a little. But you won't get around the 617937 loops, which is the cause of the long query duration. I don't think there is a lot of potential for optimization. Yours, Laurenz Albe
