explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Mtm

Settings
# exclusive inclusive rows x rows loops node
1. 0.510 3,447.631 ↑ 3.8 52 1

Insert on pg_temp_3.tt71 (cost=113,229.45..113,243.46 rows=200 width=134) (actual time=3,446.950..3,447.631 rows=52 loops=1)

  • Output: 0
  • Buffers: shared hit=25437 read=17385 dirtied=15, local hit=115 read=3 dirtied=3
2. 0.234 3,447.121 ↑ 3.8 52 1

Hash Left Join (cost=113,229.45..113,243.46 rows=200 width=134) (actual time=3,446.680..3,447.121 rows=52 loops=1)

  • Output: t3._accountrref, t10._orderfield, CASE WHEN (t3._accountrref = ANY ('{""\\\\x85bb6c0c4df4b2734ac872bf9d7990c4"",""\\\\x9a2b016ee3ac047e4203cceb2326ff6b""}'::bytea[])) THEN true ELSE false END, t10._fld461, t10._fld464, t10._offbalance, t10._fld459, t10._kind, COALESCE((CASE WHEN (t10._kind = '2'::numeric) THEN (CASE WHEN (sum(t3._fld476) > '0'::numeric) THEN sum(t3._fld476) ELSE '0'::numeric END) ELSE (CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '0'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) > '0'::numeric))) THEN sum(t3._fld476) ELSE '0'::numeric END) END)::numeric(27,2), '0'::numeric), COALESCE((CASE WHEN (t10._kind = '2'::numeric) THEN (CASE WHEN (sum(t3._fld476) < '0'::numeric) THEN (- sum(t3._fld476)) ELSE '0'::numeric END) ELSE (CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '1'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) < '0'::numeric))) THEN (- sum(t3._fld476)) ELSE '0'::numeric END) END)::numeric(27,2), '0'::numeric), (CASE WHEN (sum(t3._fld476) > '0'::numeric) THEN sum(t3._fld476) ELSE '0'::numeric END), (CASE WHEN (sum(t3._fld476) < '0'::numeric) THEN (- sum(t3._fld476)) ELSE '0'::numeric END)
  • Inner Unique: true
  • Hash Cond: (t3._accountrref = t10._idrref)
  • Buffers: shared hit=25437 read=17385 dirtied=15, local hit=12
3. 756.533 3,446.320 ↑ 3.8 52 1

HashAggregate (cost=113,205.20..113,213.20 rows=200 width=177) (actual time=3,446.015..3,446.320 rows=52 loops=1)

  • Output: t3._accountrref, CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '0'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) > '0'::numeric))) THEN sum(t3._fld476) ELSE '0'::numeric END, CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '1'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) < '0'::numeric))) THEN (- sum(t3._fld476)) ELSE '0'::numeric END, CASE WHEN (sum(t3._fld476) > '0'::numeric) THEN sum(t3._fld476) ELSE '0'::numeric END, CASE WHEN (sum(t3._fld476) < '0'::numeric) THEN (- sum(t3._fld476)) ELSE '0'::numeric END, NULL::numeric
  • Group Key: t3._accountrref
  • Filter: ((CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '0'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) > '0'::numeric))) THEN sum(t3._fld476) ELSE '0'::numeric END <> '0'::numeric) OR (CASE WHEN (sum(t3._fld476) IS NULL) THEN '0'::numeric WHEN ((max(t9._kind) = '1'::numeric) OR ((max(t9._kind) = '2'::numeric) AND (sum(t3._fld476) < '0'::numeric))) THEN (- sum(t3._fld476)) ELSE '0'::numeric END <> '0'::numeric) OR (CASE WHEN (sum(t3._fld476) > '0'::numeric) THEN sum(t3._fld476) ELSE '0'::numeric END <> '0'::numeric) OR (CASE WHEN (sum(t3._fld476) < '0'::numeric) THEN (- sum(t3._fld476)) ELSE '0'::numeric END <> '0'::numeric))
  • Rows Removed by Filter: 59
  • Buffers: shared hit=25423 read=17385 dirtied=15, local hit=12
4. 433.575 2,689.787 ↑ 1.0 955,867 1

Hash Join (cost=36.55..85,451.41 rows=957,027 width=41) (actual time=3.665..2,689.787 rows=955,867 loops=1)

  • Output: t3._accountrref, t3._fld476, t9._kind
  • Inner Unique: true
  • Hash Cond: (t3._accountrref = t9._idrref)
  • Buffers: shared hit=25423 read=17385 dirtied=15, local hit=12
5. 119.262 2,255.808 ↑ 1.0 955,867 1

Append (cost=12.29..74,871.63 rows=957,027 width=37) (actual time=3.253..2,255.808 rows=955,867 loops=1)

  • Buffers: shared hit=25409 read=17385 dirtied=15, local hit=12
6. 0.042 4.535 ↑ 1.1 62 1

Hash Semi Join (cost=12.29..193.03 rows=70 width=24) (actual time=3.252..4.535 rows=62 loops=1)

  • Output: t3._accountrref, t3._fld476
  • Hash Cond: (t3._accountrref = t4._q_000_f_000rref)
  • Buffers: shared hit=114 dirtied=7, local hit=4
7. 4.285 4.285 ↑ 1.1 63 1

Seq Scan on public._accrgat0485 t3 (cost=0.00..179.93 rows=70 width=24) (actual time=3.030..4.285 rows=63 loops=1)

  • Output: t3._accountrref, t3._period, t3._fld473rref, t3._fld474rref, t3._fld475rref, t3._fld476, t3._turnoverdt486, t3._turnoverct487, t3._turnover488, t3._fld477, t3._turnoverdt489, t3._turnoverct490, t3._turnover491, t3._fld478, t3._turnoverdt492, t3._turnoverct493, t3._turnover494, t3._fld479, t3._turnoverdt495, t3._turnoverct496, t3._turnover497, t3._fld480, t3._turnoverdt498, t3._turnoverct499, t3._turnover500, t3._fld481, t3._turnoverdt501, t3._turnoverct502, t3._turnover503, t3._fld11294, t3._splitter
  • Filter: ((t3._fld476 <> '0'::numeric) AND (t3._fld11294 = '0'::numeric) AND (t3._period = '2017-01-01 00:00:00'::timestamp without time zone) AND (t3._fld473rref = '\\x827a0013d42232d211db0c2ccc69d665'::bytea))
  • Rows Removed by Filter: 4646
  • Buffers: shared hit=114 dirtied=7
8. 0.108 0.208 ↑ 1.0 395 1

Hash (cost=7.95..7.95 rows=395 width=17) (actual time=0.208..0.208 rows=395 loops=1)

  • Output: t4._q_000_f_000rref
  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
  • Buffers: local hit=4
9. 0.100 0.100 ↑ 1.0 395 1

Seq Scan on pg_temp_3.tt70 t4 (cost=0.00..7.95 rows=395 width=17) (actual time=0.016..0.100 rows=395 loops=1)

  • Output: t4._q_000_f_000rref
  • Buffers: local hit=4
10. 363.461 1,266.967 ↑ 1.0 478,145 1

Hash Join (cost=16.64..33,035.92 rows=479,788 width=37) (actual time=0.437..1,266.967 rows=478,145 loops=1)

  • Output: t5._accountdtrref, ((- t5._fld476))::numeric(21,2)
  • Inner Unique: true
  • Hash Cond: (t5._accountdtrref = t6._q_000_f_000rref)
  • Buffers: shared hit=3955 read=17385 dirtied=8, local hit=4
11. 903.121 903.121 ↓ 1.0 480,778 1

Seq Scan on public._accrg472 t5 (cost=0.00..31,340.02 rows=479,788 width=23) (actual time=0.037..903.121 rows=480,778 loops=1)

  • Output: t5._period, t5._recordertref, t5._recorderrref, t5._lineno, t5._active, t5._accountdtrref, t5._accountctrref, t5._fld473rref, t5._fld474dtrref, t5._fld474ctrref, t5._fld475dtrref, t5._fld475ctrref, t5._fld476, t5._fld477dt, t5._fld477ct, t5._fld478dt, t5._fld478ct, t5._fld479dt, t5._fld479ct, t5._fld480dt, t5._fld480ct, t5._fld481dt, t5._fld481ct, t5._fld482, t5._fld483, t5._fld11294, t5._edhashdt, t5._edhashct
  • Filter: (t5._active AND (t5._accountdtrref <> '\\x00000000000000000000000000000000'::bytea) AND (t5._period >= '2011-01-01 00:00:00'::timestamp without time zone) AND (t5._period < '2017-01-01 00:00:00'::timestamp without time zone) AND (t5._fld11294 = '0'::numeric) AND (t5._fld473rref = '\\x827a0013d42232d211db0c2ccc69d665'::bytea))
  • Rows Removed by Filter: 185898
  • Buffers: shared hit=3955 read=17385 dirtied=8
12. 0.093 0.385 ↑ 1.0 395 1

Hash (cost=12.30..12.30 rows=395 width=17) (actual time=0.385..0.385 rows=395 loops=1)

  • Output: t6._q_000_f_000rref
  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
  • Buffers: local hit=4
13. 0.213 0.292 ↑ 1.0 395 1

HashAggregate (cost=8.35..12.30 rows=395 width=17) (actual time=0.220..0.292 rows=395 loops=1)

  • Output: t6._q_000_f_000rref
  • Group Key: t6._q_000_f_000rref
  • Buffers: local hit=4
14. 0.079 0.079 ↑ 1.0 395 1

Seq Scan on pg_temp_3.tt70 t6 (cost=0.00..7.95 rows=395 width=17) (actual time=0.008..0.079 rows=395 loops=1)

  • Output: t6._q_000_f_000rref
  • Buffers: local hit=4
15. 217.866 865.044 ↓ 1.0 477,660 1

Hash Join (cost=16.64..32,072.41 rows=477,169 width=37) (actual time=0.723..865.044 rows=477,660 loops=1)

  • Output: t7._accountctrref, (t7._fld476)::numeric(21,2)
  • Inner Unique: true
  • Hash Cond: (t7._accountctrref = t8._q_000_f_000rref)
  • Buffers: shared hit=21340, local hit=4
16. 646.579 646.579 ↓ 1.0 479,018 1

Seq Scan on public._accrg472 t7 (cost=0.00..31,340.02 rows=477,169 width=23) (actual time=0.056..646.579 rows=479,018 loops=1)

  • Output: t7._period, t7._recordertref, t7._recorderrref, t7._lineno, t7._active, t7._accountdtrref, t7._accountctrref, t7._fld473rref, t7._fld474dtrref, t7._fld474ctrref, t7._fld475dtrref, t7._fld475ctrref, t7._fld476, t7._fld477dt, t7._fld477ct, t7._fld478dt, t7._fld478ct, t7._fld479dt, t7._fld479ct, t7._fld480dt, t7._fld480ct, t7._fld481dt, t7._fld481ct, t7._fld482, t7._fld483, t7._fld11294, t7._edhashdt, t7._edhashct
  • Filter: (t7._active AND (t7._accountctrref <> '\\x00000000000000000000000000000000'::bytea) AND (t7._period >= '2011-01-01 00:00:00'::timestamp without time zone) AND (t7._period < '2017-01-01 00:00:00'::timestamp without time zone) AND (t7._fld11294 = '0'::numeric) AND (t7._fld473rref = '\\x827a0013d42232d211db0c2ccc69d665'::bytea))
  • Rows Removed by Filter: 187658
  • Buffers: shared hit=21340
17. 0.117 0.599 ↑ 1.0 395 1

Hash (cost=12.30..12.30 rows=395 width=17) (actual time=0.599..0.599 rows=395 loops=1)

  • Output: t8._q_000_f_000rref
  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
  • Buffers: local hit=4
18. 0.319 0.482 ↑ 1.0 395 1

HashAggregate (cost=8.35..12.30 rows=395 width=17) (actual time=0.414..0.482 rows=395 loops=1)

  • Output: t8._q_000_f_000rref
  • Group Key: t8._q_000_f_000rref
  • Buffers: local hit=4
19. 0.163 0.163 ↑ 1.0 395 1

Seq Scan on pg_temp_3.tt70 t8 (cost=0.00..7.95 rows=395 width=17) (actual time=0.062..0.163 rows=395 loops=1)

  • Output: t8._q_000_f_000rref
  • Buffers: local hit=4
20. 0.143 0.404 ↑ 1.0 466 1

Hash (cost=19.13..19.13 rows=466 width=21) (actual time=0.404..0.404 rows=466 loops=1)

  • Output: t9._kind, t9._idrref
  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=14
21. 0.261 0.261 ↑ 1.0 466 1

Seq Scan on public._acc15 t9 (cost=0.00..19.13 rows=466 width=21) (actual time=0.010..0.261 rows=466 loops=1)

  • Output: t9._kind, t9._idrref
  • Filter: (t9._fld11294 = '0'::numeric)
  • Buffers: shared hit=14
22. 0.214 0.567 ↑ 1.0 466 1

Hash (cost=19.13..19.13 rows=466 width=37) (actual time=0.567..0.567 rows=466 loops=1)

  • Output: t10._orderfield, t10._fld461, t10._fld464, t10._offbalance, t10._fld459, t10._kind, t10._idrref
  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared hit=14
23. 0.353 0.353 ↑ 1.0 466 1

Seq Scan on public._acc15 t10 (cost=0.00..19.13 rows=466 width=37) (actual time=0.030..0.353 rows=466 loops=1)

  • Output: t10._orderfield, t10._fld461, t10._fld464, t10._offbalance, t10._fld459, t10._kind, t10._idrref
  • Filter: (t10._fld11294 = '0'::numeric)
  • Buffers: shared hit=14
Planning time : 2.570 ms
Execution time : 3,486.377 ms