explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Ry5

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 12,291.741 ↑ 1.0 1 1

Nested Loop (cost=944,307.78..944,307.86 rows=1 width=96) (actual time=12,291.740..12,291.741 rows=1 loops=1)

2. 0.000 7,291.516 ↑ 1.0 1 1

Finalize Aggregate (cost=441,734.15..441,734.16 rows=1 width=64) (actual time=7,291.516..7,291.516 rows=1 loops=1)

3. 18.594 7,291.560 ↓ 1.5 3 1

Gather (cost=441,733.90..441,734.11 rows=2 width=96) (actual time=7,247.019..7,291.560 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 101.089 7,272.966 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=440,733.90..440,733.91 rows=1 width=96) (actual time=7,272.965..7,272.966 rows=1 loops=3)

5. 176.559 7,171.877 ↓ 2.6 258,700 3 / 3

Nested Loop (cost=266,720.83..439,999.18 rows=97,962 width=24) (actual time=2,512.102..7,171.877 rows=258,700 loops=3)

6. 979.211 6,219.211 ↓ 2.6 258,702 3 / 3

Hash Join (cost=266,720.40..383,706.78 rows=97,694 width=8) (actual time=2,512.080..6,219.211 rows=258,702 loops=3)

  • Hash Cond: (i.location_id = l.id)
  • Join Filter: ((date(timezone(l.time_zone, timezone('UTC'::text, ii.posting_date))) >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (date(timezone(l.time_zone, timezone('UTC'::text, ii.posting_date))) < '2019-12-31 23:59:59+00'::timestamp with time zone))
  • Rows Removed by Join Filter: 597,606
7. 2,192.038 5,239.952 ↑ 1.0 856,308 3 / 3

Hash Join (cost=266,716.47..381,320.67 rows=879,242 width=24) (actual time=2,511.328..5,239.952 rows=856,308 loops=3)

  • Hash Cond: (ii.invoice_id = i.id)
8. 539.668 539.668 ↑ 1.0 889,733 3 / 3

Parallel Seq Scan on invoice_item ii (cost=0.00..89,881.04 rows=919,680 width=24) (actual time=0.006..539.668 rows=889,733 loops=3)

  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 396,984
9. 1,056.506 2,508.246 ↓ 1.0 2,428,392 3 / 3

Hash (cost=225,667.20..225,667.20 rows=2,361,462 width=16) (actual time=2,508.246..2,508.246 rows=2,428,392 loops=3)

  • Buckets: 131,072 Batches: 64 Memory Usage: 2,806kB
10. 1,451.740 1,451.740 ↓ 1.0 2,428,392 3 / 3

Seq Scan on invoice i (cost=0.00..225,667.20 rows=2,361,462 width=16) (actual time=0.007..1,451.740 rows=2,428,392 loops=3)

  • 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: 109,387
11. 0.018 0.048 ↑ 1.0 41 3 / 3

Hash (cost=3.41..3.41 rows=41 width=24) (actual time=0.048..0.048 rows=41 loops=3)

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

Seq Scan on location l (cost=0.00..3.41 rows=41 width=24) (actual time=0.011..0.030 rows=41 loops=3)

13. 776.107 776.107 ↑ 1.0 1 776,107 / 3

Index Scan using invoice_item_ledger_pkey on invoice_item_ledger iil (cost=0.43..0.58 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=776,107)

  • Index Cond: (invoice_item_id = ii.id)
14. 0.000 5,000.215 ↑ 1.0 1 1

Finalize Aggregate (cost=502,573.63..502,573.65 rows=1 width=64) (actual time=5,000.214..5,000.215 rows=1 loops=1)

15. 67.468 5,001.387 ↓ 1.5 3 1

Gather (cost=502,573.38..502,573.59 rows=2 width=96) (actual time=4,897.554..5,001.387 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 97.145 4,933.919 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=501,573.38..501,573.39 rows=1 width=96) (actual time=4,933.918..4,933.919 rows=1 loops=3)

17. 172.067 4,836.774 ↓ 1.1 260,847 3 / 3

Hash Join (cost=120,068.37..499,852.58 rows=229,440 width=24) (actual time=1,048.935..4,836.774 rows=260,847 loops=3)

  • Hash Cond: (i_1.location_id = l_1.id)
18. 155.598 4,664.668 ↓ 1.1 260,847 3 / 3

Nested Loop (cost=120,064.45..499,184.12 rows=229,440 width=32) (actual time=1,048.852..4,664.668 rows=260,847 loops=3)

19. 2,138.711 3,726.277 ↓ 1.1 260,931 3 / 3

Hash Join (cost=120,064.02..225,680.95 rows=239,992 width=32) (actual time=1,048.830..3,726.277 rows=260,931 loops=3)

  • Hash Cond: (iil_1.invoice_item_id = ii_1.id)
20. 543.318 543.318 ↑ 1.3 1,286,713 3 / 3

Parallel Seq Scan on invoice_item_ledger iil_1 (cost=0.00..76,526.26 rows=1,612,826 width=32) (actual time=0.004..543.318 rows=1,286,713 loops=3)

21. 333.676 1,044.248 ↓ 1.4 782,799 3 / 3

Hash (cost=110,078.98..110,078.98 rows=574,403 width=16) (actual time=1,044.248..1,044.248 rows=782,799 loops=3)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,307kB
22. 525.101 710.572 ↓ 1.4 782,799 3 / 3

Bitmap Heap Scan on invoice_item ii_1 (cost=21,213.61..110,078.98 rows=574,403 width=16) (actual time=196.010..710.572 rows=782,799 loops=3)

  • Recheck Cond: ((created >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (created < '2019-12-31 23:59:59+00'::timestamp with time zone))
  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 228,604
  • Heap Blocks: exact=58,279
23. 185.471 185.471 ↓ 1.0 1,011,403 3 / 3

Bitmap Index Scan on invoice_item_created_idx (cost=0.00..21,070.01 rows=1,004,558 width=0) (actual time=185.471..185.471 rows=1,011,403 loops=3)

  • Index Cond: ((created >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (created < '2019-12-31 23:59:59+00'::timestamp with time zone))
24. 782.793 782.793 ↑ 1.0 1 782,793 / 3

Index Scan using invoice_pkey on invoice i_1 (cost=0.43..1.14 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=782,793)

  • 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
25. 0.015 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
26. 0.024 0.024 ↑ 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.024 rows=41 loops=3)