explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HPsu

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,962,171.897 ↓ 0.0 0 1

Update on sdm.s_s01_lnfm22_l t1 (cost=20,134,126,884.23..20,162,235,113.76 rows=25,526 width=450) (actual time=1,962,171.897..1,962,171.897 rows=0 loops=1)

  • Buffers: shared hit=550150 read=2017459 dirtied=203605 written=8288, temp read=8195788 written=8286389
2. 42.363 1,962,171.894 ↓ 0.0 0 1

Hash Join (cost=20,134,126,884.23..20,162,235,113.76 rows=25,526 width=450) (actual time=1,962,171.894..1,962,171.894 rows=0 loops=1)

  • Output: t1.l22orgno, t1.l22acct, t1.l22chat, t1.l22subn, t1.l22ccy, t1.l22flag, t1.l22lbald, t1.l22lbal, t1.l22bald, t1.l22bal, t1.l22ebald, t1.l22ebal, t1.l2
  • Hash Cond: (((t2.l22acct)::text = (t1.l22acct)::text) AND ((t2.l22chat)::text = (t1.l22chat)::text))
  • Buffers: shared hit=550150 read=2017459 dirtied=203605 written=8288, temp read=8195788 written=8286389
3. 0.002 1,879,140.743 ↓ 0.0 0 1

Subquery Scan on t2 (cost=20,131,702,647.23..20,136,891,840.54 rows=1,620,328 width=528) (actual time=1,879,140.743..1,879,140.743 rows=0 loops=1)

  • Output: t2.*, t2.l22acct, t2.l22chat
  • Buffers: shared hit=429881 read=1697906 dirtied=32674 written=8288, temp read=8195788 written=8200433
4. 22,172.133 1,879,140.741 ↓ 0.0 0 1

SetOp Intersect (cost=20,131,702,647.23..20,136,875,637.26 rows=1,620,328 width=1,206) (actual time=1,879,140.741..1,879,140.741 rows=0 loops=1)

  • Output: "*SELECT* 2".l22orgno, "*SELECT* 2".l22acct, "*SELECT* 2".l22chat, "*SELECT* 2".l22subn, "*SELECT* 2".l22ccy, "*SELECT* 2".l22flag, "*SELE
  • Buffers: shared hit=429881 read=1697906 dirtied=32674 written=8288, temp read=8195788 written=8200433
5. 1,693,771.633 1,856,968.608 ↓ 1.1 65,301,847 1

Sort (cost=20,131,702,647.23..20,131,846,341.40 rows=57,477,667 width=1,206) (actual time=1,621,879.055..1,856,968.608 rows=65,301,847 loops=1)

  • Output: "*SELECT* 2".l22orgno, "*SELECT* 2".l22acct, "*SELECT* 2".l22chat, "*SELECT* 2".l22subn, "*SELECT* 2".l22ccy, "*SELECT* 2".l22flag,
  • Sort Key: "*SELECT* 2".l22orgno, "*SELECT* 2".l22acct, "*SELECT* 2".l22chat, "*SELECT* 2".l22subn, "*SELECT* 2".l22ccy, "*SELECT* 2".l22flag
  • Sort Method: external merge Disk: 12943248kB
  • Buffers: shared hit=429881 read=1697906 dirtied=32674 written=8288, temp read=8195788 written=8200433
6. 163,196.975 163,196.975 ↓ 1.1 65,301,847 1

Append (cost=10,000,000,000.00..20,003,277,340.34 rows=57,477,667 width=1,206) (actual time=113.579..163,196.975 rows=65,301,847 loops=1)

  • Buffers: shared hit=429881 read=1697906 dirtied=32674 written=8288
  • -> Subquery Scan on "*SELECT* 2" (cost=10000000000.00..10000080688.46 rows=1718523 width=173) (actual time=113.578..10162.816 rows=1
  • Output: "*SELECT* 2".l22orgno, "*SELECT* 2".l22acct, "*SELECT* 2".l22chat, "*SELECT* 2".l22subn, "*SELECT* 2".l22ccy, "*SELECT*
  • Buffers: shared read=46318
  • -> Seq Scan on sdm.s_s01_lnfm22_t b (cost=10000000000.00..10000063503.23 rows=1718523 width=169) (actual time=113.573..9607.67
  • Output: b.l22orgno, b.l22acct, b.l22chat, b.l22subn, b.l22ccy, b.l22flag, b.l22lbald, b.l22lbal, b.l22bald, b.l22bal, b.l2
  • Buffers: shared read=46318
  • -> Subquery Scan on "*SELECT* 1" (cost=10000000000.00..10003196651.88 rows=55759144 width=172) (actual time=0.005..148070.660 rows=6
  • Output: "*SELECT* 1".l22orgno, "*SELECT* 1".l22acct, "*SELECT* 1".l22chat, "*SELECT* 1".l22subn, "*SELECT* 1".l22ccy, "*SELECT*
  • Buffers: shared hit=429881 read=1651588 dirtied=32674 written=8288
  • -> Seq Scan on sdm.s_s01_lnfm22_l a (cost=10000000000.00..10002639060.44 rows=55759144 width=168) (actual time=0.004..127819.0
  • Output: a.l22orgno, a.l22acct, a.l22chat, a.l22subn, a.l22ccy, a.l22flag, a.l22lbald, a.l22lbal, a.l22bald, a.l22bal, a.l2
  • Buffers: shared hit=429881 read=1651588 dirtied=32674 written=8288
7. 82,988.788 82,988.788 ↑ 1.2 3,612,519 1

Hash (cost=2,252,134.11..2,252,134.11 rows=4,477,459 width=166) (actual time=82,988.788..82,988.788 rows=3,612,519 loops=1)

  • Output: t1.l22orgno, t1.l22acct, t1.l22chat, t1.l22subn, t1.l22ccy, t1.l22flag, t1.l22lbald, t1.l22lbal, t1.l22bald, t1.l22bal, t1.l22ebald, t1.l22ebal,
  • Buckets: 32768 Batches: 256 Memory Usage: 3081kB
  • Buffers: shared hit=120269 read=319553 dirtied=170931, temp written=85701
  • -> Bitmap Heap Scan on sdm.s_s01_lnfm22_l t1 (cost=114696.87..2252134.11 rows=4477459 width=166) (actual time=2771.501..79144.756 rows=3612519 loops=1
  • Output: t1.l22orgno, t1.l22acct, t1.l22chat, t1.l22subn, t1.l22ccy, t1.l22flag, t1.l22lbald, t1.l22lbal, t1.l22bald, t1.l22bal, t1.l22ebald, t1.l2
  • Recheck Cond: (t1.end_dt = '3000-12-31 00:00:00'::timestamp without time zone)
  • Rows Removed by Index Recheck: 8600658
  • Heap Blocks: exact=58304 lossy=364190
  • Buffers: shared hit=120269 read=319553 dirtied=170931
  • -> Bitmap Index Scan on s_s01_lnfm22_l_end_dt_idx (cost=0.00..113577.51 rows=4477459 width=0) (actual time=2739.285..2739.285 rows=5331087 loops
  • Index Cond: (t1.end_dt = '3000-12-31 00:00:00'::timestamp without time zone)
  • Buffers: shared hit=2 read=17326