explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VCa7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 132,264.487 ↑ 2.8 4 1

Finalize GroupAggregate (cost=6,116,827.27..6,116,832.11 rows=11 width=123) (actual time=132,264.418..132,264.487 rows=4 loops=1)

  • Output: gl.ca_fs_caption, sum(gl.number_of_distinct_journals), sum(gl.number_of_journal_lines), sum(gl.total_reporting_debits), sum(abs(gl.total_reporting_credits)), sum(gl.total_reporting_net_pl)
  • Group Key: gl.ca_fs_caption
  • Buffers: shared hit=2,276,568 read=1,047,197, temp read=154,893 written=154,923
  • Functions: 75
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 14.663 ms, Inlining 169.453 ms, Optimization 1399.372 ms, Emission 855.965 ms, Total 2439.454 ms
2. 1,055.020 132,299.438 ↑ 2.1 16 1

Gather Merge (cost=6,116,827.27..6,116,831.18 rows=33 width=123) (actual time=132,264.377..132,299.438 rows=16 loops=1)

  • Output: gl.ca_fs_caption, (PARTIAL sum(gl.number_of_distinct_journals)), (PARTIAL sum(gl.number_of_journal_lines)), (PARTIAL sum(gl.total_reporting_debits)), (PARTIAL sum(abs(gl.total_reporting_credits))), (PARTIAL sum(gl.total_reporting_net_pl))
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=9,448,041 read=3,860,395, temp read=627,622 written=627,742
3. 0.029 131,244.418 ↑ 2.8 4 4 / 4

Sort (cost=6,115,827.23..6,115,827.26 rows=11 width=123) (actual time=131,244.417..131,244.418 rows=4 loops=4)

  • Output: gl.ca_fs_caption, (PARTIAL sum(gl.number_of_distinct_journals)), (PARTIAL sum(gl.number_of_journal_lines)), (PARTIAL sum(gl.total_reporting_debits)), (PARTIAL sum(abs(gl.total_reporting_credits))), (PARTIAL sum(gl.total_reporting_net_pl))
  • Sort Key: gl.ca_fs_caption
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
  • Worker 2: Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=9,448,041 read=3,860,395, temp read=627,622 written=627,742
  • Worker 0: actual time=128,854.474..128854.475 rows=4 loops=1
  • Buffers: shared hit=2,379,903 read=945,071, temp read=155,593 written=155,623
  • Worker 1: actual time=132,101.090..132101.091 rows=4 loops=1
  • Buffers: shared hit=2,427,978 read=905,050, temp read=160,516 written=160,546
  • Worker 2: actual time=132,226.919..132226.919 rows=4 loops=1
  • Buffers: shared hit=2,363,592 read=963,077, temp read=156,620 written=156,650
4. 2,205.231 131,244.389 ↑ 2.8 4 4 / 4

Partial HashAggregate (cost=6,115,826.85..6,115,827.04 rows=11 width=123) (actual time=131,244.384..131,244.389 rows=4 loops=4)

  • Output: gl.ca_fs_caption, PARTIAL sum(gl.number_of_distinct_journals), PARTIAL sum(gl.number_of_journal_lines), PARTIAL sum(gl.total_reporting_debits), PARTIAL sum(abs(gl.total_reporting_credits)), PARTIAL sum(gl.total_reporting_net_pl)
  • Group Key: gl.ca_fs_caption
  • Buffers: shared hit=9,448,017 read=3,860,395, temp read=627,622 written=627,742
  • Worker 0: actual time=128,854.443..128854.448 rows=4 loops=1
  • Buffers: shared hit=2,379,895 read=945,071, temp read=155,593 written=155,623
  • Worker 1: actual time=132,101.035..132101.040 rows=4 loops=1
  • Buffers: shared hit=2,427,970 read=905,050, temp read=160,516 written=160,546
  • Worker 2: actual time=132,226.890..132226.894 rows=4 loops=1
  • Buffers: shared hit=2,363,584 read=963,077, temp read=156,620 written=156,650
5. 14,579.995 129,039.158 ↓ 1.9 5,412,050 4 / 4

Hash Join (cost=5,152,972.41..6,066,937.05 rows=2,793,703 width=31) (actual time=108,208.517..129,039.158 rows=5,412,050 loops=4)

  • Output: gl.ca_fs_caption, gl.number_of_distinct_journals, gl.number_of_journal_lines, gl.total_reporting_debits, gl.total_reporting_credits, gl.total_reporting_net_pl
  • Inner Unique: true
  • Hash Cond: (gl.gl_business_unit_and_journal_id_computed = gl_1.gl_business_unit_and_journal_id_computed)
  • Buffers: shared hit=9,448,017 read=3,860,395, temp read=627,622 written=627,742
  • Worker 0: actual time=108,226.885..126702.373 rows=5,324,686 loops=1
  • Buffers: shared hit=2,379,895 read=945,071, temp read=155,593 written=155,623
  • Worker 1: actual time=108,058.722..129783.250 rows=5,650,498 loops=1
  • Buffers: shared hit=2,427,970 read=905,050, temp read=160,516 written=160,546
  • Worker 2: actual time=108,274.325..130019.563 rows=5,393,024 loops=1
  • Buffers: shared hit=2,363,584 read=963,077, temp read=156,620 written=156,650
6. 6,860.567 6,860.567 ↑ 1.3 10,172,680 4 / 4

Parallel Seq Scan on public.ap_8f017835_bc79_4f46_adfb_449b9d338d34_fsli_aggr gl (cost=0.00..668,805.90 rows=13,125,990 width=39) (actual time=606.752..6,860.567 rows=10,172,680 loops=4)

  • Output: gl.ca_fs_caption, gl.number_of_distinct_journals, gl.number_of_journal_lines, gl.total_reporting_debits, gl.total_reporting_credits, gl.total_reporting_net_pl, gl.gl_business_unit_and_journal_id_computed
  • Buffers: shared hit=1,252 read=536,294
  • Worker 0: actual time=616.569..6862.419 rows=10,009,277 loops=1
  • Buffers: shared hit=255 read=131,976
  • Worker 1: actual time=575.874..6899.687 rows=10,619,385 loops=1
  • Buffers: shared hit=271 read=140,014
  • Worker 2: actual time=605.776..6813.076 rows=10,137,901 loops=1
  • Buffers: shared hit=367 read=133,559
7. 2,063.120 107,598.596 ↓ 7.5 10,824,100 4 / 4

Hash (cost=5,129,417.17..5,129,417.17 rows=1,435,699 width=8) (actual time=107,598.596..107,598.596 rows=10,824,100 loops=4)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Buckets: 1,048,576 (originally 1048576) Batches: 32 (originally 4) Memory Usage: 21,420kB
  • Buffers: shared hit=9,446,564 read=3,324,101, temp read=95,412 written=238,824
  • Worker 0: actual time=107,606.311..107606.311 rows=10,824,100 loops=1
  • Buffers: shared hit=2,379,573 read=813,095, temp read=23,853 written=59,706
  • Worker 1: actual time=107,479.507..107479.507 rows=10,824,100 loops=1
  • Buffers: shared hit=2,427,632 read=765,036, temp read=23,853 written=59,706
  • Worker 2: actual time=107,664.584..107664.584 rows=10,824,100 loops=1
  • Buffers: shared hit=2,363,150 read=829,518, temp read=23,853 written=59,706
8. 2,130.364 105,535.476 ↓ 7.5 10,824,100 4 / 4

Unique (cost=5,060,649.06..5,115,060.18 rows=1,435,699 width=8) (actual time=100,082.732..105,535.476 rows=10,824,100 loops=4)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Buffers: shared hit=9,446,564 read=3,324,101, temp read=95,412 written=95,532
  • Worker 0: actual time=100,085.828..105581.651 rows=10,824,100 loops=1
  • Buffers: shared hit=2,379,573 read=813,095, temp read=23,853 written=23,883
  • Worker 1: actual time=100,080.697..105456.336 rows=10,824,100 loops=1
  • Buffers: shared hit=2,427,632 read=765,036, temp read=23,853 written=23,883
  • Worker 2: actual time=100,091.372..105571.038 rows=10,824,100 loops=1
  • Buffers: shared hit=2,363,150 read=829,518, temp read=23,853 written=23,883
9. 8,346.929 103,405.112 ↑ 1.0 10,824,109 4 / 4

Sort (cost=5,060,649.06..5,087,854.62 rows=10,882,224 width=8) (actual time=100,082.730..103,405.112 rows=10,824,109 loops=4)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Sort Key: gl_1.gl_business_unit_and_journal_id_computed
  • Sort Method: external merge Disk: 190,824kB
  • Worker 0: Sort Method: external merge Disk: 190,824kB
  • Worker 1: Sort Method: external merge Disk: 190,824kB
  • Worker 2: Sort Method: external merge Disk: 190,824kB
  • Buffers: shared hit=9,446,564 read=3,324,101, temp read=95,412 written=95,532
  • Worker 0: actual time=100,085.827..103482.371 rows=10,824,109 loops=1
  • Buffers: shared hit=2,379,573 read=813,095, temp read=23,853 written=23,883
  • Worker 1: actual time=100,080.694..103348.609 rows=10,824,109 loops=1
  • Buffers: shared hit=2,427,632 read=765,036, temp read=23,853 written=23,883
  • Worker 2: actual time=100,091.370..103423.516 rows=10,824,109 loops=1
  • Buffers: shared hit=2,363,150 read=829,518, temp read=23,853 written=23,883
10. 95,058.183 95,058.183 ↑ 1.0 10,824,109 4 / 4

Seq Scan on public.ap_8f017835_bc79_4f46_adfb_449b9d338d34_mega_gl gl_1 (cost=0.00..3,701,620.10 rows=10,882,224 width=8) (actual time=17.294..95,058.183 rows=10,824,109 loops=4)

  • Output: gl_1.gl_business_unit_and_journal_id_computed
  • Filter: (gl_1.ca_fs_caption = 'Cash'::text)
  • Rows Removed by Filter: 29,892,990
  • Buffers: shared hit=9,446,543 read=3,324,101
  • Worker 0: actual time=28.810..95145.756 rows=10,824,109 loops=1
  • Buffers: shared hit=2,379,566 read=813,095
  • Worker 1: actual time=0.111..95149.533 rows=10,824,109 loops=1
  • Buffers: shared hit=2,427,625 read=765,036
  • Worker 2: actual time=40.217..95138.509 rows=10,824,109 loops=1
  • Buffers: shared hit=2,363,143 read=829,518
Execution time : 132,330.754 ms