0

We have a postgres DB on version 11.2. We are running the below query and the objective is to insert into a staging table. The number of records inserted are only 82. But the performance varies dramatically when we do an insert. Just selecting the query or creating a new table happens very quickly, but on inserting, the plan changes to nested loop.

Number of records per table is given below

abs."TELEKGERAET" a - 230k dm_work.stg_asset_details_full - 8.7mill dm_work.stg_incr_policy incr - 69k

How do we improve the performance of the insert query?

explain analyze
insert into dm_work.stg_gdt_dtls1 --( "VERTR#", imei_no, gad_make, model, gad_sum_insured, gad_type, year_of_launch, gad_purchase_date)
select incr."VERTR#", upper(trim(ad1.IMEI_NO)) imei_no, upper(trim(ad1.gdt[1])) as gad_make, upper(trim(ad1.gdt[2])) model,ad1.gad_sum_insured,
upper(trim(ad1.asset_type)) gad_type, ad1.asset_yom as year_of_launch, incr.begdat gad_purchase_date 
from 
(select ad."VERTR#", ad."VERSV#", asset_type, asset_yom, a."SACH#", "EGERWERT" gad_sum_insured, "EGERTYPBEZ" AS IMEI_NO,
string_to_array(a."EGERMARKE"::text, '|':: text) as gdt 
from abs."TELEKGERAET" a , dm_work.stg_asset_details_full ad
where a."SACH#" = ad."SACH#") ad1,
dm_work.stg_incr_policy incr
where incr."VERTR#" = ad1."VERTR#"
and incr."VERSV#" = ad1."VERSV#"

QUERY PLAN
Insert on stg_gdt_dtls1  (cost=3839.20..476006.73 rows=1 width=182) (actual time=3166637.502..3166637.502 rows=0 loops=1)
  ->  Nested Loop  (cost=3839.20..476006.73 rows=1 width=182) (actual time=83436.889..3166636.624 rows=82 loops=1)
        Join Filter: ((ad."SACH#")::text = (a."SACH#")::text)
        Rows Removed by Join Filter: 15275660027
        ->  Hash Join  (cost=3839.20..463137.11 rows=1 width=96) (actual time=2969.537..3211.038 rows=51501 loops=1)
              Hash Cond: (((ad."VERTR#")::text = (incr."VERTR#")::text) AND (ad."VERSV#" = incr."VERSV#"))
              ->  Seq Scan on stg_asset_details_full ad  (cost=0.00..328229.76 rows=8737876 width=125) (actual time=0.005..947.431 rows=8737876 loops=1)
              ->  Hash  (cost=2798.08..2798.08 rows=69408 width=82) (actual time=30.401..30.402 rows=69408 loops=1)
                    Buckets: 131072  Batches: 1  Memory Usage: 8955kB
                    ->  Seq Scan on stg_incr_policy incr  (cost=0.00..2798.08 rows=69408 width=82) (actual time=0.006..14.310 rows=69408 loops=1)
        ->  Seq Scan on "TELEKGERAET" a  (cost=0.00..9162.04 rows=296604 width=95) (actual time=0.002..27.850 rows=296609 loops=51501)
Planning Time: 3.559 ms
Execution Time: 3166637.581 ms


explain analyze
select incr."VERTR#", upper(trim(ad1.IMEI_NO)) imei_no, upper(trim(ad1.gdt[1])) as gad_make, upper(trim(ad1.gdt[2])) model,ad1.gad_sum_insured,
upper(trim(ad1.asset_type)) gad_type, ad1.asset_yom as year_of_launch, incr.begdat gad_purchase_date 
from 
(select ad."VERTR#", ad."VERSV#", asset_type, asset_yom, a."SACH#", "EGERWERT" gad_sum_insured, "EGERTYPBEZ" AS IMEI_NO,
string_to_array(a."EGERMARKE"::text, '|':: text) as gdt 
from abs."TELEKGERAET" a , dm_work.stg_asset_details_full ad
where a."SACH#" = ad."SACH#") ad1,
dm_work.stg_incr_policy incr
where incr."VERTR#" = ad1."VERTR#"
and incr."VERSV#" = ad1."VERSV#"

QUERY PLAN
Gather  (cost=13456.24..289046.88 rows=1 width=182) (actual time=5194.707..5252.815 rows=105 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Hash Join  (cost=12456.24..288046.78 rows=1 width=182) (actual time=5186.548..5235.930 rows=21 loops=5)
        Hash Cond: (((ad."VERTR#")::text = (incr."VERTR#")::text) AND (ad."VERSV#" = incr."VERSV#"))
        ->  Parallel Hash Join  (cost=8979.56..282222.88 rows=187775 width=146) (actual time=116.791..5151.112 rows=52898 loops=5)
              Hash Cond: ((ad."SACH#")::text = (a."SACH#")::text)
              ->  Parallel Seq Scan on stg_asset_details_full ad  (cost=0.00..264221.58 rows=2197158 width=125) (actual time=0.410..4471.587 rows=1756067 loops=5)
              ->  Parallel Hash  (cost=7434.25..7434.25 rows=123625 width=95) (actual time=115.781..115.781 rows=59339 loops=5)
                    Buckets: 524288  Batches: 1  Memory Usage: 41440kB
                    ->  Parallel Seq Scan on "TELEKGERAET" a  (cost=0.00..7434.25 rows=123625 width=95) (actual time=0.906..78.866 rows=59339 loops=5)
        ->  Parallel Hash  (cost=2795.27..2795.27 rows=45427 width=82) (actual time=59.260..59.260 rows=15445 loops=5)
              Buckets: 131072  Batches: 1  Memory Usage: 10208kB
              ->  Parallel Seq Scan on stg_incr_policy incr  (cost=0.00..2795.27 rows=45427 width=82) (actual time=0.517..49.116 rows=15445 loops=5)
Planning Time: 66.960 ms
Execution Time: 5252.897 ms


explain analyze
create table dm_work.stg_gdt_dtls1 as  
select incr."VERTR#", upper(trim(ad1.IMEI_NO)) imei_no, upper(trim(ad1.gdt[1])) as gad_make, upper(trim(ad1.gdt[2])) model,ad1.gad_sum_insured,
upper(trim(ad1.asset_type)) gad_type, ad1.asset_yom as year_of_launch, incr.begdat gad_purchase_date 
from 
(select ad."VERTR#", ad."VERSV#", asset_type, asset_yom, a."SACH#", "EGERWERT" gad_sum_insured, "EGERTYPBEZ" AS IMEI_NO,
string_to_array(a."EGERMARKE"::text, '|':: text) as gdt 
from abs."TELEKGERAET" a , dm_work.stg_asset_details_full ad
where a."SACH#" = ad."SACH#") ad1,
dm_work.stg_incr_policy incr
where incr."VERTR#" = ad1."VERTR#"
and incr."VERSV#" = ad1."VERSV#"

QUERY PLAN
Gather  (cost=13456.24..289046.88 rows=1 width=182) (actual time=845.933..859.545 rows=105 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Parallel Hash Join  (cost=12456.24..288046.78 rows=1 width=182) (actual time=841.659..845.696 rows=21 loops=5)
        Hash Cond: (((ad."VERTR#")::text = (incr."VERTR#")::text) AND (ad."VERSV#" = incr."VERSV#"))
        ->  Parallel Hash Join  (cost=8979.56..282222.88 rows=187775 width=146) (actual time=48.605..811.481 rows=52898 loops=5)
              Hash Cond: ((ad."SACH#")::text = (a."SACH#")::text)
              ->  Parallel Seq Scan on stg_asset_details_full ad  (cost=0.00..264221.58 rows=2197158 width=125) (actual time=0.005..262.213 rows=1756067 loops=5)
              ->  Parallel Hash  (cost=7434.25..7434.25 rows=123625 width=95) (actual time=48.069..48.070 rows=59339 loops=5)
                    Buckets: 524288  Batches: 1  Memory Usage: 41408kB
                    ->  Parallel Seq Scan on "TELEKGERAET" a  (cost=0.00..7434.25 rows=123625 width=95) (actual time=0.006..15.484 rows=59339 loops=5)
        ->  Parallel Hash  (cost=2795.27..2795.27 rows=45427 width=82) (actual time=13.151..13.151 rows=15445 loops=5)
              Buckets: 131072  Batches: 1  Memory Usage: 10176kB
              ->  Parallel Seq Scan on stg_incr_policy incr  (cost=0.00..2795.27 rows=45427 width=82) (actual time=0.004..4.863 rows=15445 loops=5)
Planning Time: 3.465 ms
Execution Time: 876.688 ms
1

Had created an index on "TELEKGERAET" as it was doing a seq scan. This helped and now the execution time is 5 secs. Thank you Laurenz for all the pointers.

  • Embarrassing. How could I miss that. – Laurenz Albe Oct 16 at 10:18
0

The parallel plan happens to be good, but statement that write data cannot use parallel plans. Unfortunately the parallel plan turns out to be bad.

The underlying reason is the gross mis-estimate of the number of result rows for the hash join.

Perhaps extended statistics will help:

CREATE STATISTICS good_name
   ON "VERTR#", "VERSV#"
   FROM dm_work.stg_asset_details_full;

ANALYZE dm_work.stg_asset_details_full;
  • Thanks for the reply Laurenz. However even after the extended stats, the plan remains the same. There's a primary key on stg_asset_details_full_pk PRIMARY KEY ("VERTR#", "VERSV#", "PAP#", "SACH#") along with an index on dm_work.stg_asset_details_full ("SACH#") – vishal Oct 16 at 8:42
  • Can you try the similar extended statistics on stg_incr_policy? – Laurenz Albe Oct 16 at 8:58
  • Also, try increasing default_statistics_target and ANALYZEing the tables again. – Laurenz Albe Oct 16 at 9:02
  • default_statistics_target is set to 1000. extended statistics on stg_incr_policy also does not help. Dropped the PK and recreated it with a different order ("PAP#", "SACH#","VERTR#", "VERSV#") and created an index on "VERTR#", "VERSV#". This also did not help. This seems strange. – vishal Oct 16 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.