explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LRBi

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 36,991.426 ↑ 17.0 2 1

Finalize GroupAggregate (cost=1,083,860.33..1,083,870.98 rows=34 width=50) (actual time=36,991.421..36,991.426 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. 10.206 36,991.774 ↑ 11.3 6 1

Gather Merge (cost=1,083,860.33..1,083,869.70 rows=68 width=50) (actual time=36,991.376..36,991.774 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.217 36,981.568 ↑ 17.0 2 3 / 3

Partial GroupAggregate (cost=1,082,860.30..1,082,861.83 rows=34 width=50) (actual time=36,981.545..36,981.568 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=36,979.652..36979.678 rows=2 loops=1
  • Worker 1: actual time=36,978.801..36978.818 rows=2 loops=1
4. 0.132 36,981.351 ↑ 1.5 57 3 / 3

Sort (cost=1,082,860.30..1,082,860.52 rows=88 width=20) (actual time=36,981.345..36,981.351 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: 30kB
  • Worker 0: Sort Method: quicksort Memory: 29kB
  • Worker 1: Sort Method: quicksort Memory: 28kB
  • Worker 0: actual time=36,979.620..36979.625 rows=56 loops=1
  • Worker 1: actual time=36,978.269..36978.274 rows=48 loops=1
5. 2.211 36,981.219 ↑ 1.5 57 3 / 3

Hash Join (cost=35,093.55..1,082,857.46 rows=88 width=20) (actual time=4,186.679..36,981.219 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,183.292..36979.463 rows=56 loops=1
  • Worker 1: actual time=4,176.201..36978.121 rows=48 loops=1
6. 3,862.996 36,978.018 ↑ 1.7 10,491 3 / 3

Parallel Hash Join (cost=34,552.03..1,082,269.26 rows=17,766 width=18) (actual time=4,174.288..36,978.018 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,172.212..36976.445 rows=8,084 loops=1
  • Worker 1: actual time=4,172.225..36974.819 rows=12,433 loops=1
7. 32,864.602 32,864.602 ↑ 1.3 21,016,848 3 / 3

Parallel Seq Scan on office.posted_transaction_item pti (cost=0.00..978,689.75 rows=26,296,175 width=24) (actual time=0.607..32,864.602 rows=21,016,848 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.726..32897.231 rows=20,869,282 loops=1
  • Worker 1: actual time=0.570..32861.317 rows=20,972,639 loops=1
8. 1.058 250.420 ↑ 1.3 1,764 3 / 3

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

  • Output: pt.id, pt.company
  • Buckets: 8,192 Batches: 1 Memory Usage: 320kB
  • Worker 0: actual time=248.000..248.000 rows=1,788 loops=1
  • Worker 1: actual time=248.742..248.742 rows=1,889 loops=1
9. 206.844 249.362 ↑ 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=124.219..249.362 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=250
  • Worker 0: actual time=121.858..246.859 rows=1,788 loops=1
  • Worker 1: actual time=122.437..247.838 rows=1,889 loops=1
10. 42.518 42.518 ↑ 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=127.554..127.554 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.008 0.990 ↑ 11.3 3 3 / 3

Hash (cost=541.10..541.10 rows=34 width=18) (actual time=0.990..0.990 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.121..1.121 rows=3 loops=1
  • Worker 1: actual time=0.852..0.852 rows=3 loops=1
12. 0.023 0.982 ↑ 11.3 3 3 / 3

Bitmap Heap Scan on office.gl (cost=427.51..541.10 rows=34 width=18) (actual time=0.974..0.982 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.103..1.113 rows=3 loops=1
  • Worker 1: actual time=0.831..0.842 rows=3 loops=1
13. 0.959 0.959 ↑ 11.3 3 3 / 3

Bitmap Index Scan on idx__gl__account_number (cost=0.00..427.50 rows=34 width=0) (actual time=0.959..0.959 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.085..1.085 rows=3 loops=1
  • Worker 1: actual time=0.813..0.813 rows=3 loops=1
Planning time : 2.941 ms
Execution time : 36,992.442 ms