pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNested Loops
2+ messages / 2 participants
[nested] [flat]
* Nested Loops
@ 2018-01-31 06:37 Kumar, Virendra <Virendra.Kumar@guycarp.com>
2018-01-31 08:42 ` Re: Nested Loops Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 2+ messages in thread
From: Kumar, Virendra @ 2018-01-31 06:37 UTC (permalink / raw)
To: 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
________________________________
This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Nested Loops
2018-01-31 06:37 Nested Loops Kumar, Virendra <Virendra.Kumar@guycarp.com>
@ 2018-01-31 08:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2018-01-31 08:42 UTC (permalink / raw)
To: Kumar, Virendra <Virendra.Kumar@guycarp.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-01-31 08:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-31 06:37 Nested Loops Kumar, Virendra <Virendra.Kumar@guycarp.com>
2018-01-31 08:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
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