explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7p2E

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 12,508.226 ↓ 0.0 0 1

Update on public.fact_accrual (cost=1,076,076.73..1,540,257.75 rows=3,548,686 width=370) (actual time=12,508.226..12,508.226 rows=0 loops=1)

  • Buffers: shared hit=16 read=271540 written=106
  • I/O Timings: read=4443.681 write=2.212
  • Functions: 46
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.033 ms, Inlining 110.000 ms, Optimization 821.472 ms, Emission 578.067 ms, Total 1520.572 ms
2.          

CTE tmp

3. 4,202.805 12,508.219 ↓ 0.0 0 1

Hash Left Join (cost=2.44..504,032.05 rows=3,548,686 width=132) (actual time=12,508.218..12,508.219 rows=0 loops=1)

  • Output: pf.accrual_sk, (pf.accrued_quantity - COALESCE(pfx.accrued_quantity, '0'::numeric)), (pf.accrued_amount_direct - COALESCE(pfx.accrued_amount_direct, '0'::numeric)), (pf.accrued_amount_deviated - COALESCE(pfx.accrued_amount_deviated, '0'::numeric)), (pf.accrued_amount_lumpsum - COALESCE(pfx.accrued_amount_lumpsum, '0'::numeric))
  • Hash Cond: ((pf.client_sk = pfx.client_sk) AND (pf.contract_family_sk = pfx.contract_family_sk) AND (pf.contract_sk = pfx.contract_sk) AND (pf.product_sk = pfx.product_sk) AND (pf.lumpsum_sk = pfx.lumpsum_sk) AND (pf.accrual_company_sk = pfx.accrual_company_sk) AND (pf.accrual_date = pfx.accrual_date))
  • Filter: ((pf.accrued_quantity_delta IS DISTINCT FROM (pf.accrued_quantity - COALESCE(pfx.accrued_quantity, '0'::numeric))) OR (pf.accrued_amount_direct_delta IS DISTINCT FROM (pf.accrued_amount_direct - COALESCE(pfx.accrued_amount_direct, '0'::numeric))) OR (pf.accrued_amount_deviated_delta IS DISTINCT FROM (pf.accrued_amount_deviated - COALESCE(pfx.accrued_amount_deviated, '0'::numeric))) OR (pf.accrued_amount_lumpsum_delta IS DISTINCT FROM (pf.accrued_amount_lumpsum - COALESCE(pfx.accrued_amount_lumpsum, '0'::numeric))))
  • Rows Removed by Filter: 3549608
  • Buffers: shared hit=16 read=271540 written=106
  • I/O Timings: read=4443.681 write=2.212
4. 8,303.507 8,303.507 ↓ 1.0 3,549,608 1

Seq Scan on public.fact_accrual pf (cost=0.00..375,389.71 rows=3,548,686 width=93) (actual time=1,510.733..8,303.507 rows=3,549,608 loops=1)

  • Output: pf.accrual_sk, pf.client_sk, pf.contract_family_sk, pf.contract_renew_version_sk, pf.contract_amend_version_sk, pf.product_sk, pf.lumpsum_sk, pf.accrual_company_sk, pf.accrual_date, pf.uom_sk, pf.accrued_quantity, pf.accrued_amount_direct, pf.accrued_amount_deviated, pf.accrued_amount_lumpsum, pf.accrual_run_sk, pf.contract_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 = 4254)
  • Rows Removed by Filter: 4722796
  • Buffers: shared hit=8 read=271538 written=106
  • I/O Timings: read=4441.844 write=2.212
5. 0.002 1.907 ↓ 0.0 0 1

Hash (cost=2.41..2.41 rows=1 width=60) (actual time=1.907..1.907 rows=0 loops=1)

  • Output: pfx.accrued_quantity, pfx.accrued_amount_direct, pfx.accrued_amount_deviated, pfx.accrued_amount_lumpsum, pfx.client_sk, pfx.contract_family_sk, pfx.contract_sk, pfx.product_sk, pfx.lumpsum_sk, pfx.accrual_company_sk, pfx.accrual_date
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=1.836
6. 1.905 1.905 ↓ 0.0 0 1

Index Scan using fact_accrual_accrual_run_sk_idx on public.fact_accrual pfx (cost=0.56..2.41 rows=1 width=60) (actual time=1.905..1.905 rows=0 loops=1)

  • Output: pfx.accrued_quantity, pfx.accrued_amount_direct, pfx.accrued_amount_deviated, pfx.accrued_amount_lumpsum, pfx.client_sk, pfx.contract_family_sk, pfx.contract_sk, pfx.product_sk, pfx.lumpsum_sk, pfx.accrual_company_sk, pfx.accrual_date
  • Index Cond: (pfx.accrual_run_sk = '-2'::integer)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=1.836
7. 0.001 12,508.223 ↓ 0.0 0 1

Hash Join (cost=572,044.68..1,036,225.70 rows=3,548,686 width=370) (actual time=12,508.223..12,508.223 rows=0 loops=1)

  • Output: fact_accrual.accrual_sk, fact_accrual.client_sk, fact_accrual.contract_family_sk, fact_accrual.contract_renew_version_sk, fact_accrual.contract_amend_version_sk, fact_accrual.product_sk, fact_accrual.lumpsum_sk, fact_accrual.accrual_company_sk, fact_accrual.accrual_date, fact_accrual.uom_sk, fact_accrual.accrued_quantity, fact_accrual.accrued_amount_direct, fact_accrual.accrued_amount_deviated, fact_accrual.accrued_amount_lumpsum, fact_accrual.accrual_run_sk, fact_accrual.contract_sk, tmp.accrued_quantity_delta, tmp.accrued_amount_direct_delta, tmp.accrued_amount_deviated_delta, tmp.accrued_amount_lumpsum_delta, fact_accrual.ctid, tmp.*
  • Inner Unique: true
  • Hash Cond: (tmp.accrual_sk = fact_accrual.accrual_sk)
  • Buffers: shared hit=16 read=271540 written=106
  • I/O Timings: read=4443.681 write=2.212
8. 12,508.222 12,508.222 ↓ 0.0 0 1

CTE Scan on tmp (cost=0.00..70,973.72 rows=3,548,686 width=288) (actual time=12,508.222..12,508.222 rows=0 loops=1)

  • Output: tmp.accrued_quantity_delta, tmp.accrued_amount_direct_delta, tmp.accrued_amount_deviated_delta, tmp.accrued_amount_lumpsum_delta, tmp.*, tmp.accrual_sk
  • Buffers: shared hit=16 read=271540 written=106
  • I/O Timings: read=4443.681 write=2.212
9. 0.000 0.000 ↓ 0.0 0

Hash (cost=354,620.97..354,620.97 rows=8,307,497 width=86) (never executed)

  • Output: fact_accrual.accrual_sk, fact_accrual.client_sk, fact_accrual.contract_family_sk, fact_accrual.contract_renew_version_sk, fact_accrual.contract_amend_version_sk, fact_accrual.product_sk, fact_accrual.lumpsum_sk, fact_accrual.accrual_company_sk, fact_accrual.accrual_date, fact_accrual.uom_sk, fact_accrual.accrued_quantity, fact_accrual.accrued_amount_direct, fact_accrual.accrued_amount_deviated, fact_accrual.accrued_amount_lumpsum, fact_accrual.accrual_run_sk, fact_accrual.contract_sk, fact_accrual.ctid
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.fact_accrual (cost=0.00..354,620.97 rows=8,307,497 width=86) (never executed)

  • Output: fact_accrual.accrual_sk, fact_accrual.client_sk, fact_accrual.contract_family_sk, fact_accrual.contract_renew_version_sk, fact_accrual.contract_amend_version_sk, fact_accrual.product_sk, fact_accrual.lumpsum_sk, fact_accrual.accrual_company_sk, fact_accrual.accrual_date, fact_accrual.uom_sk, fact_accrual.accrued_quantity, fact_accrual.accrued_amount_direct, fact_accrual.accrued_amount_deviated, fact_accrual.accrued_amount_lumpsum, fact_accrual.accrual_run_sk, fact_accrual.contract_sk, fact_accrual.ctid
Execution time : 12,719.288 ms