explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gpS3

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,230.925 ↑ 1.0 1 1

Finalize Aggregate (cost=116,657.76..116,657.77 rows=1 width=64) (actual time=2,230.925..2,230.925 rows=1 loops=1)

2. 4.783 2,232.151 ↓ 1.5 3 1

Gather (cost=116,657.51..116,657.72 rows=2 width=96) (actual time=2,230.605..2,232.151 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 92.881 2,227.368 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=115,657.51..115,657.52 rows=1 width=96) (actual time=2,227.368..2,227.368 rows=1 loops=3)

4. 292.254 2,134.487 ↓ 58.7 258,703 3 / 3

Nested Loop (cost=412.99..115,624.44 rows=4,409 width=24) (actual time=74.195..2,134.487 rows=258,703 loops=3)

5. 155.597 1,324.822 ↓ 58.8 258,705 3 / 3

Hash Join (cost=412.56..85,962.49 rows=4,397 width=8) (actual time=74.182..1,324.822 rows=258,705 loops=3)

  • Hash Cond: (i.location_id = l.id)
6. 331.375 1,169.188 ↓ 58.8 258,705 3 / 3

Nested Loop (cost=408.64..85,945.84 rows=4,397 width=16) (actual time=74.105..1,169.188 rows=258,705 loops=3)

7. 297.712 320.235 ↓ 56.3 258,789 3 / 3

Parallel Bitmap Heap Scan on invoice_item ii (cost=408.21..53,096.17 rows=4,599 width=16) (actual time=74.081..320.235 rows=258,789 loops=3)

  • Recheck Cond: ((posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
  • Rows Removed by Index Recheck: 190,895
  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 75,617
  • Heap Blocks: exact=14,116 lossy=10,894
8. 22.523 22.523 ↓ 52.0 1,003,217 1 / 3

Bitmap Index Scan on invoice_item_posting_date_ref_idx (cost=0.00..405.45 rows=19,302 width=0) (actual time=67.570..67.570 rows=1,003,217 loops=1)

  • Index Cond: ((posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
9. 517.578 517.578 ↑ 1.0 1 776,367 / 3

Index Scan using invoice_pkey on invoice i (cost=0.43..7.14 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=776,367)

  • Index Cond: (id = ii.invoice_id)
  • Filter: ((NOT void) AND (location_id = ANY ('{0,27,8,16,44,18,14,21,32,25,24,15,30,31,34,10,1,19,17,13,7,45,46,33,23,42,12,11,29,4,28,43,26,20,41,22,6,5,2,3}'::bigint[])))
  • Rows Removed by Filter: 0
10. 0.016 0.037 ↑ 1.0 41 3 / 3

Hash (cost=3.41..3.41 rows=41 width=8) (actual time=0.037..0.037 rows=41 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
11. 0.021 0.021 ↑ 1.0 41 3 / 3

Seq Scan on location l (cost=0.00..3.41 rows=41 width=8) (actual time=0.008..0.021 rows=41 loops=3)

12. 517.411 517.411 ↑ 1.0 1 776,116 / 3

Index Scan using invoice_item_ledger_pkey on invoice_item_ledger iil (cost=0.43..6.75 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=776,116)

  • Index Cond: (invoice_item_id = ii.id)