explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hS7k

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 40,201.501 ↑ 17.0 2 1

Finalize GroupAggregate (cost=1,083,855.94..1,083,866.59 rows=34 width=50) (actual time=40,201.494..40,201.501 rows=2 loops=1)

  • Output: gl.account_number, sum(pti.amount), sum(pti.unit_count), gl.account_type
  • Group Key: gl.account_number, gl.account_type
2. 17.156 40,205.761 ↑ 11.3 6 1

Gather Merge (cost=1,083,855.94..1,083,865.31 rows=68 width=50) (actual time=40,201.454..40,205.761 rows=6 loops=1)

  • Output: gl.account_number, gl.account_type, (PARTIAL sum(pti.amount)), (PARTIAL sum(pti.unit_count))
  • Workers Planned: 2
  • Workers Launched: 2
3. 0.057 40,188.605 ↑ 17.0 2 3 / 3

Partial GroupAggregate (cost=1,082,855.91..1,082,857.44 rows=34 width=50) (actual time=40,188.585..40,188.605 rows=2 loops=3)

  • Output: gl.account_number, gl.account_type, PARTIAL sum(pti.amount), PARTIAL sum(pti.unit_count)
  • Group Key: gl.account_number, gl.account_type
  • Worker 0: actual time=40,185.575..40185.593 rows=2 loops=1
  • Worker 1: actual time=40,180.078..40180.099 rows=2 loops=1
4. 0.154 40,188.548 ↑ 1.5 57 3 / 3

Sort (cost=1,082,855.91..1,082,856.13 rows=88 width=20) (actual time=40,188.543..40,188.548 rows=57 loops=3)

  • Output: gl.account_number, gl.account_type, pti.amount, pti.unit_count
  • Sort Key: gl.account_number, gl.account_type
  • Sort Method: quicksort Memory: 29kB
  • Worker 0: Sort Method: quicksort Memory: 30kB
  • Worker 1: Sort Method: quicksort Memory: 28kB
  • Worker 0: actual time=40,185.539..40185.544 rows=66 loops=1
  • Worker 1: actual time=40,180.022..40180.027 rows=50 loops=1
5. 2.436 40,188.394 ↑ 1.5 57 3 / 3

Hash Join (cost=35,093.55..1,082,853.07 rows=88 width=20) (actual time=4,723.886..40,188.394 rows=57 loops=3)

  • Output: gl.account_number, gl.account_type, pti.amount, pti.unit_count
  • Inner Unique: true
  • Hash Cond: (pti.cobol_key = gl.cobol_key)
  • Worker 0: actual time=4,728.015..40185.360 rows=66 loops=1
  • Worker 1: actual time=4,720.145..40179.842 rows=50 loops=1
6. 4,071.137 40,184.829 ↑ 1.7 10,491 3 / 3

Parallel Hash Join (cost=34,552.03..1,082,264.88 rows=17,766 width=18) (actual time=4,707.750..40,184.829 rows=10,491 loops=3)

  • Output: pt.company, pti.amount, pti.unit_count, pti.cobol_key
  • Inner Unique: true
  • Hash Cond: (pti.transaction_id = pt.id)
  • Worker 0: actual time=4,704.377..40181.708 rows=10,157 loops=1
  • Worker 1: actual time=4,698.885..40175.870 rows=12,512 loops=1
7. 35,698.904 35,698.904 ↑ 1.3 21,016,794 3 / 3

Parallel Seq Scan on office.posted_transaction_item pti (cost=0.00..978,685.65 rows=26,296,065 width=24) (actual time=0.779..35,698.904 rows=21,016,794 loops=3)

  • Output: pti.transaction_id, pti.lineno, pti.cobol_key, pti.amount, pti.cost, pti.control_number, pti.apply_to, pti.comments, pti.unit_count, pti.trend_ext_flag, pti.bank_rec_cleared, pti.item_type
  • Worker 0: actual time=0.571..35687.475 rows=21,039,626 loops=1
  • Worker 1: actual time=1.301..35714.467 rows=20,998,427 loops=1
8. 1.100 414.788 ↑ 1.3 1,764 3 / 3

Parallel Hash (cost=34,523.18..34,523.18 rows=2,308 width=8) (actual time=414.788..414.788 rows=1,764 loops=3)

  • Output: pt.id, pt.company
  • Buckets: 8,192 Batches: 1 Memory Usage: 320kB
  • Worker 0: actual time=411.775..411.776 rows=1,739 loops=1
  • Worker 1: actual time=406.290..406.290 rows=1,605 loops=1
9. 321.168 413.688 ↑ 1.3 1,764 3 / 3

Parallel Bitmap Heap Scan on office.posted_transaction pt (cost=16,637.17..34,523.18 rows=2,308 width=8) (actual time=266.915..413.688 rows=1,764 loops=3)

  • Output: pt.id, pt.company
  • Recheck Cond: ((pt.company = 1) AND (pt.transaction_date >= '2020-06-01'::date) AND (pt.transaction_date <= '2020-06-29'::date))
  • Filter: ((pt.source <> '09'::text) AND (pt.source <> 'YE'::text))
  • Heap Blocks: exact=265
  • Worker 0: actual time=264.311..410.571 rows=1,739 loops=1
  • Worker 1: actual time=258.231..405.246 rows=1,605 loops=1
10. 92.520 92.520 ↑ 1.0 5,292 1 / 3

Bitmap Index Scan on posted_transaction_company_source_transaction_date_referenc_key (cost=0.00..16,635.78 rows=5,538 width=0) (actual time=277.560..277.560 rows=5,292 loops=1)

  • Index Cond: ((pt.company = 1) AND (pt.transaction_date >= '2020-06-01'::date) AND (pt.transaction_date <= '2020-06-29'::date))
11. 0.009 1.129 ↑ 11.3 3 3 / 3

Hash (cost=541.10..541.10 rows=34 width=18) (actual time=1.129..1.129 rows=3 loops=3)

  • Output: gl.account_number, gl.account_type, gl.cobol_key, gl.company_number
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Worker 0: actual time=1.178..1.178 rows=3 loops=1
  • Worker 1: actual time=1.144..1.144 rows=3 loops=1
12. 0.036 1.120 ↑ 11.3 3 3 / 3

Bitmap Heap Scan on office.gl (cost=427.51..541.10 rows=34 width=18) (actual time=1.110..1.120 rows=3 loops=3)

  • Output: gl.account_number, gl.account_type, gl.cobol_key, gl.company_number
  • Recheck Cond: ((gl.company_number = 1) AND (gl.account_number = ANY ('{090A,414L,690,402L,450,692,451,693,452,694,644A,695,454,696,455,697,456,458,091F,091D,415L,403L,091C,091A,645A,658Q,080F,080D,416L,092F,404L,092C,080C,092D,470,092A,080A,472,473,474,475,611T,417L,405L,081F,081C,480,081D,081A,482,483,484,485,002,611A,003,004,488,611B,489,006,007,008,082A,418L,406L,082F,490,070F,070C,082D,070D,492,070A,493,082C,494,070B,495,012,019,611V,071A,419L,407L,071F,071D,020,071C,021,022,023,025,026,027,028,072A,060A,084C,084A,408L,060F,060D,084F,072F,072C,084D,060C,072D,061A,085C,085D,085A,061F,061C,061D,085F,409L,074C,074D,074A,99998,99999,062F,051,062D,074F,063C,075D,051C,063D,087F,063A,087C,051A,075C,087D,087A,075A,060,061,051F,051D,075F,064,063F,066,068,069,088F,076F,076C,088D,076D,076A,088C,088A,070,071,072,074,075,076,078,075Z,089F,089C,089D,089A,080,082,084,085,087,088,089,066F,066D,078F,078C,066C,078D,078A,066A,090,091,092,096,097,099,067F,067D,020D,068F,020C,068D,020A,068C,005BC,020F}'::text[])))
  • Heap Blocks: exact=2
  • Worker 0: actual time=1.154..1.168 rows=3 loops=1
  • Worker 1: actual time=1.122..1.134 rows=3 loops=1
13. 1.084 1.084 ↑ 11.3 3 3 / 3

Bitmap Index Scan on idx__gl__account_number (cost=0.00..427.50 rows=34 width=0) (actual time=1.084..1.084 rows=3 loops=3)

  • Index Cond: ((gl.company_number = 1) AND (gl.account_number = ANY ('{090A,414L,690,402L,450,692,451,693,452,694,644A,695,454,696,455,697,456,458,091F,091D,415L,403L,091C,091A,645A,658Q,080F,080D,416L,092F,404L,092C,080C,092D,470,092A,080A,472,473,474,475,611T,417L,405L,081F,081C,480,081D,081A,482,483,484,485,002,611A,003,004,488,611B,489,006,007,008,082A,418L,406L,082F,490,070F,070C,082D,070D,492,070A,493,082C,494,070B,495,012,019,611V,071A,419L,407L,071F,071D,020,071C,021,022,023,025,026,027,028,072A,060A,084C,084A,408L,060F,060D,084F,072F,072C,084D,060C,072D,061A,085C,085D,085A,061F,061C,061D,085F,409L,074C,074D,074A,99998,99999,062F,051,062D,074F,063C,075D,051C,063D,087F,063A,087C,051A,075C,087D,087A,075A,060,061,051F,051D,075F,064,063F,066,068,069,088F,076F,076C,088D,076D,076A,088C,088A,070,071,072,074,075,076,078,075Z,089F,089C,089D,089A,080,082,084,085,087,088,089,066F,066D,078F,078C,066C,078D,078A,066A,090,091,092,096,097,099,067F,067D,020D,068F,020C,068D,020A,068C,005BC,020F}'::text[])))
  • Worker 0: actual time=1.108..1.108 rows=3 loops=1
  • Worker 1: actual time=1.098..1.098 rows=3 loops=1
Planning time : 3.482 ms
Execution time : 40,206.502 ms