explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SxP6

Settings
# exclusive inclusive rows x rows loops node
1. 16,206.565 60,846.604 ↓ 0.0 0 1

Update on pg_temp_7.tmp_fact_accrual (cost=502,129.50..1,013,554.78 rows=1 width=846) (actual time=60,846.604..60,846.604 rows=0 loops=1)

  • Buffers: local hit=7713150 read=774888 dirtied=762910 written=996467
  • I/O Timings: read=16597.701
  • Functions: 19
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 8.609 ms, Inlining 21.423 ms, Optimization 617.121 ms, Emission 324.281 ms, Total 971.434 ms
2.          

CTE tmp

3. 4,278.437 13,637.847 ↓ 3,574,274.0 3,574,274 1

Nested Loop Left Join (cost=0.00..502,129.47 rows=1 width=144) (actual time=0.064..13,637.847 rows=3,574,274 loops=1)

  • Output: pf.row_id, (pf.accrued_quantity - COALESCE(accrued_quantity, '0'::numeric)), (pf.accrued_amount_direct - COALESCE(accrued_amount_direct, '0'::numeric)), (pf.accrued_amount_deviated - COALESCE(accrued_amount_deviated, '0'::numeric)), (pf.accrued_amount_lumpsum - COALESCE(accrued_amount_lumpsum, '0'::numeric))
  • Join Filter: false
  • Filter: ((pf.accrued_quantity_delta IS DISTINCT FROM (pf.accrued_quantity - COALESCE(accrued_quantity, '0'::numeric))) OR (pf.accrued_amount_direct_delta IS DISTINCT FROM (pf.accrued_amount_direct - COALESCE(accrued_amount_direct, '0'::numeric))) OR (pf.accrued_amount_deviated_delta IS DISTINCT FROM (pf.accrued_amount_deviated - COALESCE(accrued_amount_deviated, '0'::numeric))) OR (pf.accrued_amount_lumpsum_delta IS DISTINCT FROM (pf.accrued_amount_lumpsum - COALESCE(accrued_amount_lumpsum, '0'::numeric))))
  • Buffers: local hit=43493 read=365678 dirtied=313654 written=313653
  • I/O Timings: read=3223.975
4. 9,359.410 9,359.410 ↓ 3,574,274.0 3,574,274 1

Seq Scan on pg_temp_7.tmp_fact_accrual pf (cost=0.00..502,129.42 rows=1 width=188) (actual time=0.045..9,359.410 rows=3,574,274 loops=1)

  • Output: pf.contract_amend_version_id, pf.contract_renew_version_id, pf.contract_family_id, pf.contract_template_id, pf.contractee_company_id, pf.contractee_grouping_id, pf.accrual_company_id, pf.contract_item_id, pf.lumpsum_id, pf.contract_lumpsum_id, pf.product_id, pf.accrued_quantity, pf.accrued_quantity_uom_type_id, pf.accrued_amount_direct, pf.accrued_amount_deviated, pf.accrued_amount_lumpsum, pf.accrual_date, pf.row_id, pf.accrual_run_sk, pf.contract_family_sk, pf.contract_renew_version_sk, pf.contract_amend_version_sk, pf.contract_sk, pf.product_sk, pf.lumpsum_sk, pf.accrual_company_sk, pf.uom_sk, pf.accrued_quantity_delta, pf.accrued_amount_direct_delta, pf.accrued_amount_deviated_delta, pf.accrued_amount_lumpsum_delta
  • Filter: (pf.accrual_run_sk = 4255)
  • Buffers: local hit=43493 read=365678 dirtied=313654 written=313653
  • I/O Timings: read=3223.975
5. 0.000 0.000 ↓ 0.0 0 3,574,274

Result (cost=0.00..0.00 rows=0 width=23) (actual time=0.000..0.000 rows=0 loops=3,574,274)

  • Output: accrued_quantity, accrued_amount_direct, accrued_amount_deviated, accrued_amount_lumpsum
  • One-Time Filter: false
6. 4,577.654 44,640.039 ↓ 3,574,274.0 3,574,274 1

Hash Join (cost=0.03..511,425.31 rows=1 width=846) (actual time=20,184.871..44,640.039 rows=3,574,274 loops=1)

  • Output: tmp_fact_accrual.contract_amend_version_id, tmp_fact_accrual.contract_renew_version_id, tmp_fact_accrual.contract_family_id, tmp_fact_accrual.contract_template_id, tmp_fact_accrual.contractee_company_id, tmp_fact_accrual.contractee_grouping_id, tmp_fact_accrual.accrual_company_id, tmp_fact_accrual.contract_item_id, tmp_fact_accrual.lumpsum_id, tmp_fact_accrual.contract_lumpsum_id, tmp_fact_accrual.product_id, tmp_fact_accrual.accrued_quantity, tmp_fact_accrual.accrued_quantity_uom_type_id, tmp_fact_accrual.accrued_amount_direct, tmp_fact_accrual.accrued_amount_deviated, tmp_fact_accrual.accrued_amount_lumpsum, tmp_fact_accrual.accrual_date, tmp_fact_accrual.row_id, tmp_fact_accrual.accrual_run_sk, tmp_fact_accrual.contract_family_sk, tmp_fact_accrual.contract_renew_version_sk, tmp_fact_accrual.contract_amend_version_sk, tmp_fact_accrual.contract_sk, tmp_fact_accrual.product_sk, tmp_fact_accrual.lumpsum_sk, tmp_fact_accrual.accrual_company_sk, tmp_fact_accrual.uom_sk, tmp.accrued_quantity_delta, tmp.accrued_amount_direct_delta, tmp.accrued_amount_deviated_delta, tmp.accrued_amount_lumpsum_delta, tmp_fact_accrual.ctid, tmp.*
  • Hash Cond: (tmp_fact_accrual.row_id = tmp.row_id)
  • Buffers: local hit=43514 read=774828 dirtied=313654 written=596795
  • I/O Timings: read=16594.055
7. 20,840.784 20,840.784 ↑ 2.1 3,574,274 1

Seq Scan on pg_temp_7.tmp_fact_accrual (cost=0.00..483,537.74 rows=7,436,674 width=550) (actual time=963.212..20,840.784 rows=3,574,274 loops=1)

  • Output: tmp_fact_accrual.contract_amend_version_id, tmp_fact_accrual.contract_renew_version_id, tmp_fact_accrual.contract_family_id, tmp_fact_accrual.contract_template_id, tmp_fact_accrual.contractee_company_id, tmp_fact_accrual.contractee_grouping_id, tmp_fact_accrual.accrual_company_id, tmp_fact_accrual.contract_item_id, tmp_fact_accrual.lumpsum_id, tmp_fact_accrual.contract_lumpsum_id, tmp_fact_accrual.product_id, tmp_fact_accrual.accrued_quantity, tmp_fact_accrual.accrued_quantity_uom_type_id, tmp_fact_accrual.accrued_amount_direct, tmp_fact_accrual.accrued_amount_deviated, tmp_fact_accrual.accrued_amount_lumpsum, tmp_fact_accrual.accrual_date, tmp_fact_accrual.row_id, tmp_fact_accrual.accrual_run_sk, tmp_fact_accrual.contract_family_sk, tmp_fact_accrual.contract_renew_version_sk, tmp_fact_accrual.contract_amend_version_sk, tmp_fact_accrual.contract_sk, tmp_fact_accrual.product_sk, tmp_fact_accrual.lumpsum_sk, tmp_fact_accrual.accrual_company_sk, tmp_fact_accrual.uom_sk, tmp_fact_accrual.ctid
  • Buffers: local hit=21 read=409150 written=283142
  • I/O Timings: read=13370.079
8. 2,221.177 19,221.601 ↓ 3,574,274.0 3,574,274 1

Hash (cost=0.02..0.02 rows=1 width=312) (actual time=19,221.601..19,221.601 rows=3,574,274 loops=1)

  • Output: tmp.accrued_quantity_delta, tmp.accrued_amount_direct_delta, tmp.accrued_amount_deviated_delta, tmp.accrued_amount_lumpsum_delta, tmp.*, tmp.row_id
  • Buckets: 4194304 (originally 1024) Batches: 1 (originally 1) Memory Usage: 643954kB
  • Buffers: local hit=43493 read=365678 dirtied=313654 written=313653
  • I/O Timings: read=3223.975
9. 17,000.424 17,000.424 ↓ 3,574,274.0 3,574,274 1

CTE Scan on tmp (cost=0.00..0.02 rows=1 width=312) (actual time=0.076..17,000.424 rows=3,574,274 loops=1)

  • Output: tmp.accrued_quantity_delta, tmp.accrued_amount_direct_delta, tmp.accrued_amount_deviated_delta, tmp.accrued_amount_lumpsum_delta, tmp.*, tmp.row_id
  • Buffers: local hit=43493 read=365678 dirtied=313654 written=313653
  • I/O Timings: read=3223.975
Execution time : 61,035.146 ms