explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZVz

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 79,104.665 ↑ 17.0 2 1

Finalize GroupAggregate (cost=1,086,385.94..1,086,396.80 rows=34 width=50) (actual time=79,104.662..79,104.665 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. 13.796 79,105.060 ↑ 34.0 2 1

Gather Merge (cost=1,086,385.94..1,086,395.53 rows=68 width=50) (actual time=79,104.587..79,105.060 rows=2 loops=1)

  • Output: gl.account_number, gl.account_type, (PARTIAL sum(pti.amount)), (PARTIAL sum(pti.unit_count))
  • Workers Planned: 2
  • Workers Launched: 0
3. 0.151 79,091.264 ↑ 17.0 2 1

Partial GroupAggregate (cost=1,085,385.92..1,085,387.66 rows=34 width=50) (actual time=79,091.204..79,091.264 rows=2 loops=1)

  • Output: gl.account_number, gl.account_type, PARTIAL sum(pti.amount), PARTIAL sum(pti.unit_count)
  • Group Key: gl.account_number, gl.account_type
4. 0.245 79,091.113 ↓ 1.6 172 1

Sort (cost=1,085,385.92..1,085,386.18 rows=105 width=20) (actual time=79,091.096..79,091.113 rows=172 loops=1)

  • Output: gl.account_number, gl.account_type, pti.amount, pti.unit_count
  • Sort Key: gl.account_number, gl.account_type
  • Sort Method: quicksort Memory: 38kB
5. 7.721 79,090.868 ↓ 1.6 172 1

Hash Join (cost=38,377.54..1,085,382.39 rows=105 width=20) (actual time=14,711.065..79,090.868 rows=172 loops=1)

  • Output: gl.account_number, gl.account_type, pti.amount, pti.unit_count
  • Inner Unique: true
  • Hash Cond: (pti.cobol_key = gl.cobol_key)
6. 25,124.071 79,061.194 ↓ 1.5 31,473 1

Parallel Hash Join (cost=37,822.45..1,084,771.99 rows=21,060 width=18) (actual time=7,536.162..79,061.194 rows=31,473 loops=1)

  • Output: pt.company, pti.amount, pti.unit_count, pti.cobol_key
  • Inner Unique: true
  • Hash Cond: (pti.transaction_id = pt.id)
7. 53,326.421 53,326.421 ↓ 2.4 62,945,365 1

Parallel Seq Scan on office.posted_transaction_item pti (cost=0.00..977,958.16 rows=26,282,416 width=24) (actual time=0.012..53,326.421 rows=62,945,365 loops=1)

  • 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
8. 1.594 610.702 ↓ 1.9 5,292 1

Parallel Hash (cost=37,788.29..37,788.29 rows=2,733 width=8) (actual time=610.701..610.702 rows=5,292 loops=1)

  • Output: pt.id, pt.company
  • Buckets: 8,192 Batches: 1 Memory Usage: 288kB
9. 6.955 609.108 ↓ 1.9 5,292 1

Parallel Bitmap Heap Scan on office.posted_transaction pt (cost=17,086.83..37,788.29 rows=2,733 width=8) (actual time=602.312..609.108 rows=5,292 loops=1)

  • 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=745
10. 602.153 602.153 ↑ 1.2 5,296 1

Bitmap Index Scan on posted_transaction_company_source_transaction_date_referenc_key (cost=0.00..17,085.19 rows=6,559 width=0) (actual time=602.153..602.153 rows=5,296 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.024 21.953 ↑ 11.3 3 1

Hash (cost=554.66..554.66 rows=34 width=18) (actual time=21.953..21.953 rows=3 loops=1)

  • Output: gl.account_number, gl.account_type, gl.cobol_key, gl.company_number
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 3.438 21.929 ↑ 11.3 3 1

Bitmap Heap Scan on office.gl (cost=439.51..554.66 rows=34 width=18) (actual time=21.911..21.929 rows=3 loops=1)

  • 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
13. 18.491 18.491 ↑ 11.3 3 1

Bitmap Index Scan on uq__co_account_number (cost=0.00..439.50 rows=34 width=0) (actual time=18.491..18.491 rows=3 loops=1)

  • 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[])))
Planning time : 5.658 ms
Execution time : 79,105.943 ms