explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D13I

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 4,511.701 ↑ 1.0 1 1

Nested Loop (cost=233,315.52..233,315.60 rows=1 width=96) (actual time=4,511.700..4,511.701 rows=1 loops=1)

2. 0.000 2,262.936 ↑ 1.0 1 1

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

3. 4.211 2,264.196 ↓ 1.5 3 1

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

  • Workers Planned: 2
  • Workers Launched: 2
4. 95.742 2,259.985 ↑ 1.0 1 3 / 3

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

5. 302.996 2,164.243 ↓ 58.7 258,703 3 / 3

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

6. 160.239 1,343.836 ↓ 58.8 258,705 3 / 3

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

  • Hash Cond: (i.location_id = l.id)
7. 341.678 1,183.558 ↓ 58.8 258,705 3 / 3

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

8. 301.946 324.302 ↓ 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=73.915..324.302 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,175 lossy=11,132
9. 22.356 22.356 ↓ 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.067..67.067 rows=1,003,217 loops=1)

  • Index Cond: ((posting_date_ref >= '2019-01-01'::date) AND (posting_date_ref <= '2019-12-31'::date))
10. 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
11. 0.017 0.039 ↑ 1.0 41 3 / 3

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

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

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

13. 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)
14. 0.000 2,248.754 ↑ 1.0 1 1

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

15. 4.100 2,249.953 ↓ 1.5 3 1

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

  • Workers Planned: 2
  • Workers Launched: 2
16. 94.504 2,245.853 ↑ 1.0 1 3 / 3

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

17. 292.402 2,151.349 ↓ 59.2 260,875 3 / 3

Nested Loop (cost=412.99..115,624.44 rows=4,409 width=24) (actual time=74.996..2,151.349 rows=260,875 loops=3)

18. 158.895 1,337.194 ↓ 59.3 260,877 3 / 3

Hash Join (cost=412.56..85,962.49 rows=4,397 width=8) (actual time=74.983..1,337.194 rows=260,877 loops=3)

  • Hash Cond: (i_1.location_id = l_1.id)
19. 332.547 1,178.258 ↓ 59.3 260,877 3 / 3

Nested Loop (cost=408.64..85,945.84 rows=4,397 width=16) (actual time=74.898..1,178.258 rows=260,877 loops=3)

20. 301.153 323.789 ↓ 56.7 260,961 3 / 3

Parallel Bitmap Heap Scan on invoice_item ii_1 (cost=408.21..53,096.17 rows=4,599 width=16) (actual time=74.873..323.789 rows=260,961 loops=3)

  • Recheck Cond: ((created_date_ref >= '2019-01-01'::date) AND (created_date_ref <= '2019-12-31'::date))
  • Rows Removed by Index Recheck: 187,856
  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 76,210
  • Heap Blocks: exact=14,047 lossy=11,058
21. 22.636 22.636 ↓ 52.4 1,011,514 1 / 3

Bitmap Index Scan on invoice_item_created_date_ref_idx (cost=0.00..405.45 rows=19,302 width=0) (actual time=67.907..67.907 rows=1,011,514 loops=1)

  • Index Cond: ((created_date_ref >= '2019-01-01'::date) AND (created_date_ref <= '2019-12-31'::date))
22. 521.922 521.922 ↑ 1.0 1 782,883 / 3

Index Scan using invoice_pkey on invoice i_1 (cost=0.43..7.14 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=782,883)

  • Index Cond: (id = ii_1.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
23. 0.019 0.041 ↑ 1.0 41 3 / 3

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

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

Seq Scan on location l_1 (cost=0.00..3.41 rows=41 width=8) (actual time=0.009..0.022 rows=41 loops=3)

25. 521.753 521.753 ↑ 1.0 1 782,630 / 3

Index Scan using invoice_item_ledger_pkey on invoice_item_ledger iil_1 (cost=0.43..6.75 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=782,630)

  • Index Cond: (invoice_item_id = ii_1.id)