Message-ID: <1517388179.2579.15.camel@cybertec.at>
Subject: Re: Nested Loops
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Kumar, Virendra" <Virendra.Kumar@guycarp.com>,
	"pgsql-performance@postgresql.org"
	 <pgsql-performance@postgresql.org>
Date: Wed, 31 Jan 2018 09:42:59 +0100
In-Reply-To: <91e77e2bd0494c8e9c20859e39df7553@USFKL11XG20CN01.mercer.com>
References: <91e77e2bd0494c8e9c20859e39df7553@USFKL11XG20CN01.mercer.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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