explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4JnGc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 37,307.699 ↑ 17.0 2 1

Finalize GroupAggregate (cost=1,083,807.31..1,083,817.96 rows=34 width=50) (actual time=37,307.692..37,307.699 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. 48.161 37,342.553 ↑ 11.3 6 1

Gather Merge (cost=1,083,807.31..1,083,816.68 rows=68 width=50) (actual time=37,307.638..37,342.553 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.062 37,294.392 ↑ 17.0 2 3 / 3

Partial GroupAggregate (cost=1,082,807.29..1,082,808.81 rows=34 width=50) (actual time=37,294.365..37,294.392 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=37,287.516..37287.534 rows=2 loops=1
  • Worker 1: actual time=37,295.267..37295.301 rows=2 loops=1
4. 0.183 37,294.330 ↑ 1.5 57 3 / 3

Sort (cost=1,082,807.29..1,082,807.51 rows=88 width=20) (actual time=37,294.322..37,294.330 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: 28kB
  • Worker 0: Sort Method: quicksort Memory: 29kB
  • Worker 1: Sort Method: quicksort Memory: 30kB
  • Worker 0: actual time=37,287.473..37287.478 rows=53 loops=1
  • Worker 1: actual time=37,295.218..37295.227 rows=68 loops=1
5. 2.319 37,294.147 ↑ 1.5 57 3 / 3

Hash Join (cost=35,088.84..1,082,804.44 rows=88 width=20) (actual time=8,249.250..37,294.147 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=8,226.989..37287.289 rows=53 loops=1
  • Worker 1: actual time=8,219.706..37294.949 rows=68 loops=1
6. 3,805.093 37,290.515 ↑ 1.7 10,491 3 / 3

Parallel Hash Join (cost=34,547.31..1,082,216.24 rows=17,767 width=18) (actual time=3,230.308..37,290.515 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=3,214.742..37283.814 rows=10,410 loops=1
  • Worker 1: actual time=3,249.983..37291.350 rows=10,782 loops=1
7. 33,287.599 33,287.599 ↑ 1.3 21,016,125 3 / 3

Parallel Seq Scan on office.posted_transaction_item pti (cost=0.00..978,644.62 rows=26,294,962 width=24) (actual time=0.848..33,287.599 rows=21,016,125 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=1.614..33286.895 rows=20,985,089 loops=1
  • Worker 1: actual time=0.028..33296.850 rows=21,054,817 loops=1
8. 2.033 197.823 ↑ 1.3 1,764 3 / 3

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

  • Output: pt.id, pt.company
  • Buckets: 8,192 Batches: 1 Memory Usage: 320kB
  • Worker 0: actual time=193.036..193.036 rows=1,685 loops=1
  • Worker 1: actual time=195.165..195.165 rows=1,355 loops=1
9. 142.344 195.790 ↑ 1.3 1,764 3 / 3

Parallel Bitmap Heap Scan on office.posted_transaction pt (cost=16,632.62..34,518.46 rows=2,308 width=8) (actual time=153.427..195.790 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=335
  • Worker 0: actual time=149.344..192.391 rows=1,685 loops=1
  • Worker 1: actual time=150.422..192.520 rows=1,355 loops=1
10. 53.446 53.446 ↑ 1.0 5,292 1 / 3

Bitmap Index Scan on posted_transaction_company_source_transaction_date_referenc_key (cost=0.00..16,631.23 rows=5,538 width=0) (actual time=160.338..160.338 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.313 ↑ 11.3 3 3 / 3

Hash (cost=541.10..541.10 rows=34 width=18) (actual time=1.313..1.313 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.205..1.205 rows=3 loops=1
  • Worker 1: actual time=1.152..1.152 rows=3 loops=1
12. 0.041 1.304 ↑ 11.3 3 3 / 3

Bitmap Heap Scan on office.gl (cost=427.51..541.10 rows=34 width=18) (actual time=1.287..1.304 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.182..1.195 rows=3 loops=1
  • Worker 1: actual time=1.120..1.141 rows=3 loops=1
13. 1.263 1.263 ↑ 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.262..1.263 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.157..1.157 rows=3 loops=1
  • Worker 1: actual time=1.093..1.093 rows=3 loops=1
Planning time : 16.648 ms
Execution time : 37,343.102 ms