Re: Nested Loops

Поиск
Список
Период
Сортировка
От Laurenz Albe
Тема Re: Nested Loops
Дата
Msg-id 1517388179.2579.15.camel@cybertec.at
обсуждение исходный текст
Ответ на Nested Loops  ("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.489rows=255 loops=6)
 
>                            Buffers: shared hit=864235
>                            ->  Append  (cost=0.00..156424.56 rows=123645 width=48) (actual time=1.011..204.748
rows=102990loops=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)
(actualtime=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=45width=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




В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kumar, Virendra"
Дата:
Сообщение: Nested Loops
Следующее
От: Vitaliy Garnashevich
Дата:
Сообщение: effective_io_concurrency on EBS/gp2