explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qUbl

Settings
# exclusive inclusive rows x rows loops node
1. 1,306.561 13,816.107 ↓ 2.0 53,911 1

Finalize GroupAggregate (cost=146,736.29..152,020.41 rows=26,755 width=300) (actual time=11,869.611..13,816.107 rows=53,911 loops=1)

  • Output: loanid, COALESCE(sum(principalcurrent), '0'::numeric), COALESCE(sum(principaloverdue), '0'::numeric), COALESCE(sum(interestcurrent), '0'::numeric), COALESCE(sum(interestoverdue), '0'::numeric), COALESCE(sum(penaltiesfixedtotal), '0'::numeric), COALESCE(sum(penaltiesvartotal), '0'::numeric), COALESCE(sum(additionalitemsum), '0'::numeric), max(billingdate), NULL::unknown, NULL::unknown
  • Group Key: la.loanid
  • Buffers: shared hit=30546, temp read=24647 written=24647
2. 1,939.281 12,509.546 ↓ 2.2 236,790 1

Sort (cost=146,736.29..147,003.84 rows=107,020 width=236) (actual time=11,869.572..12,509.546 rows=236,790 loops=1)

  • Output: loanid, (PARTIAL sum(principalcurrent)), (PARTIAL sum(principaloverdue)), (PARTIAL sum(interestcurrent)), (PARTIAL sum(interestoverdue)), (PARTIAL sum(penaltiesfixedtotal)), (PARTIAL sum(penaltiesvartotal)), (PARTIAL sum(additionalitemsum)), (PARTIAL max(billingdate))
  • Sort Key: la.loanid
  • Sort Method: external merge Disk: 67360kB
  • Buffers: shared hit=30546, temp read=24647 written=24647
3. 1,442.118 10,570.265 ↓ 2.2 236,790 1

Gather (cost=101,138.79..125,724.60 rows=107,020 width=236) (actual time=4,062.278..10,570.265 rows=236,790 loops=1)

  • Output: loanid, (PARTIAL sum(principalcurrent)), (PARTIAL sum(principaloverdue)), (PARTIAL sum(interestcurrent)), (PARTIAL sum(interestoverdue)), (PARTIAL sum(penaltiesfixedtotal)), (PARTIAL sum(penaltiesvartotal)), (PARTIAL sum(additionalitemsum)), (PARTIAL max(billingdate))
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=30546, temp read=12936 written=12936
4. 2,675.377 9,128.147 ↓ 1.8 47,358 5 / 5

Partial GroupAggregate (cost=100,138.79..114,022.60 rows=26,755 width=236) (actual time=3,966.616..9,128.147 rows=47,358 loops=5)

  • Output: loanid, PARTIAL sum(principalcurrent), PARTIAL sum(principaloverdue), PARTIAL sum(interestcurrent), PARTIAL sum(interestoverdue), PARTIAL sum(penaltiesfixedtotal), PARTIAL sum(penaltiesvartotal), PARTIAL sum(additionalitemsum), PARTIAL max(billingdate)
  • Group Key: la.loanid
  • Buffers: shared hit=30546, temp read=12936 written=12936
  • Worker 0: actual time=3893.784..8661.193 rows=47266 loops=1
  • Buffers: shared hit=5412, temp read=2297 written=2297
  • Worker 1: actual time=3994.923..10024.161 rows=48355 loops=1
  • Buffers: shared hit=7117, temp read=3026 written=3026
  • Worker 2: actual time=4016.790..8846.452 rows=46643 loops=1
  • Buffers: shared hit=5556, temp read=2337 written=2337
  • Worker 3: actual time=3866.186..8408.105 rows=45895 loops=1
  • Buffers: shared hit=5200, temp read=2204 written=2204
5. 4,697.736 6,452.770 ↑ 1.2 421,002 5 / 5

Sort (cost=100,138.79..101,453.59 rows=525,922 width=34) (actual time=3,966.306..6,452.770 rows=421,002 loops=5)

  • Output: loanid, principalcurrent, principaloverdue, interestcurrent, interestoverdue, penaltiesfixedtotal, penaltiesvartotal, additionalitemsum, billingdate
  • Sort Key: la.loanid
  • Sort Method: external merge Disk: 24512kB
  • Buffers: shared hit=30546, temp read=12936 written=12936
  • Worker 0: actual time=3893.727..6117.383 rows=373737 loops=1
  • Buffers: shared hit=5412, temp read=2297 written=2297
  • Worker 1: actual time=3994.439..6850.307 rows=492394 loops=1
  • Buffers: shared hit=7117, temp read=3026 written=3026
  • Worker 2: actual time=4016.285..6307.614 rows=380252 loops=1
  • Buffers: shared hit=5556, temp read=2337 written=2337
  • Worker 3: actual time=3865.719..6109.124 rows=358791 loops=1
  • Buffers: shared hit=5200, temp read=2204 written=2204
6. 1,755.034 1,755.034 ↑ 1.2 421,002 5 / 5

Parallel Seq Scan on public.loanallocation la (cost=0.00..35,782.89 rows=525,922 width=34) (actual time=0.036..1,755.034 rows=421,002 loops=5)

  • Output: loanid, principalcurrent, principaloverdue, interestcurrent, interestoverdue, penaltiesfixedtotal, penaltiesvartotal, additionalitemsum, billingdate
  • Filter: la.isactive
  • Rows Removed by Filter: 7223
  • Buffers: shared hit=30430
  • Worker 0: actual time=0.061..1671.467 rows=373737 loops=1
  • Buffers: shared hit=5383
  • Worker 1: actual time=0.034..1897.393 rows=492394 loops=1
  • Buffers: shared hit=7088
  • Worker 2: actual time=0.033..1591.331 rows=380252 loops=1
  • Buffers: shared hit=5527
  • Worker 3: actual time=0.035..1810.511 rows=358791 loops=1
  • Buffers: shared hit=5171