From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Nested Loops
Date: Wed, 31 Jan 2018 09:42:59 +0100
Message-ID: <1517388179.2579.15.camel@cybertec.at> (raw)
In-Reply-To: <91e77e2bd0494c8e9c20859e39df7553@USFKL11XG20CN01.mercer.com>
References: <91e77e2bd0494c8e9c20859e39df7553@USFKL11XG20CN01.mercer.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, Virendra.Kumar@guycarp.com
Subject: Re: Nested Loops
In-Reply-To: <1517388179.2579.15.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox