Re: Nested Loops

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