explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wDLP

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 10,199.635 ↑ 1.0 1 1

Nested Loop (cost=1,018,081.85..1,018,081.94 rows=1 width=96) (actual time=10,199.633..10,199.635 rows=1 loops=1)

2. 0.000 4,944.071 ↑ 1.0 1 1

Finalize Aggregate (cost=515,508.01..515,508.03 rows=1 width=64) (actual time=4,944.071..4,944.071 rows=1 loops=1)

3. 99.880 4,944.116 ↓ 1.5 3 1

Gather (cost=515,507.77..515,507.98 rows=2 width=96) (actual time=4,787.316..4,944.116 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 98.743 4,844.236 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=514,507.77..514,507.78 rows=1 width=96) (actual time=4,844.235..4,844.236 rows=1 loops=3)

5. 173.449 4,745.493 ↓ 1.2 258,700 3 / 3

Hash Join (cost=136,568.81..512,836.22 rows=222,872 width=24) (actual time=1,806.766..4,745.493 rows=258,700 loops=3)

  • Hash Cond: (i.location_id = l.id)
6. 174.470 4,572.004 ↓ 1.2 258,700 3 / 3

Nested Loop (cost=136,564.89..512,186.78 rows=222,872 width=32) (actual time=1,806.681..4,572.004 rows=258,700 loops=3)

7. 1,205.205 3,621.182 ↓ 1.1 258,784 3 / 3

Hash Join (cost=136,564.46..242,101.39 rows=233,122 width=32) (actual time=1,806.660..3,621.182 rows=258,784 loops=3)

  • Hash Cond: (iil.invoice_item_id = ii.id)
8. 613.233 613.233 ↑ 1.3 1,286,714 3 / 3

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

9. 346.447 1,802.744 ↓ 1.4 776,358 3 / 3

Hash (cost=126,864.96..126,864.96 rows=557,960 width=16) (actual time=1,802.744..1,802.744 rows=776,358 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3287kB
10. 532.237 1,456.297 ↓ 1.4 776,358 3 / 3

Bitmap Heap Scan on invoice_item ii (cost=23,793.93..126,864.96 rows=557,960 width=16) (actual time=934.954..1,456.297 rows=776,358 loops=3)

  • Recheck Cond: ((date(timezone('America/New_York'::text, timezone('UTC'::text, posting_date))) >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (date(timezone('America/New_York'::text, timezone('UTC'::text, posting_date))) < '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: 226842
  • Heap Blocks: exact=60043
11. 924.060 924.060 ↓ 1.3 1,274,543 3 / 3

Bitmap Index Scan on invoice_item_posting_date_new_york_idx (cost=0.00..23,654.44 rows=975,801 width=0) (actual time=924.060..924.060 rows=1,274,543 loops=3)

  • Index Cond: ((date(timezone('America/New_York'::text, timezone('UTC'::text, posting_date))) >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (date(timezone('America/New_York'::text, timezone('UTC'::text, posting_date))) < '2019-12-31 23:59:59+00'::timestamp with time zone))
12. 776.352 776.352 ↑ 1.0 1 776,352 / 3

Index Scan using invoice_pkey on invoice i (cost=0.43..1.16 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=776,352)

  • 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
13. 0.017 0.040 ↑ 1.0 41 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
14. 0.023 0.023 ↑ 1.0 41 3 / 3

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

15. 0.000 5,255.550 ↑ 1.0 1 1

Finalize Aggregate (cost=502,573.84..502,573.85 rows=1 width=64) (actual time=5,255.550..5,255.550 rows=1 loops=1)

16. 76.542 5,256.773 ↓ 1.5 3 1

Gather (cost=502,573.59..502,573.80 rows=2 width=96) (actual time=5,159.112..5,256.773 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 97.392 5,180.231 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=501,573.59..501,573.60 rows=1 width=96) (actual time=5,180.230..5,180.231 rows=1 loops=3)

18. 170.808 5,082.839 ↓ 1.1 260,847 3 / 3

Hash Join (cost=120,068.31..499,852.78 rows=229,440 width=24) (actual time=1,071.553..5,082.839 rows=260,847 loops=3)

  • Hash Cond: (i_1.location_id = l_1.id)
19. 166.233 4,911.991 ↓ 1.1 260,847 3 / 3

Nested Loop (cost=120,064.39..499,184.32 rows=229,440 width=32) (actual time=1,071.471..4,911.991 rows=260,847 loops=3)

20. 2,326.056 3,962.965 ↓ 1.1 260,931 3 / 3

Hash Join (cost=120,063.96..225,680.89 rows=239,992 width=32) (actual time=1,071.448..3,962.965 rows=260,931 loops=3)

  • Hash Cond: (iil_1.invoice_item_id = ii_1.id)
21. 571.628 571.628 ↑ 1.3 1,286,714 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..571.628 rows=1,286,714 loops=3)

22. 344.309 1,065.281 ↓ 1.4 782,799 3 / 3

Hash (cost=110,078.93..110,078.93 rows=574,402 width=16) (actual time=1,065.281..1,065.281 rows=782,799 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3307kB
23. 537.423 720.972 ↓ 1.4 782,799 3 / 3

Bitmap Heap Scan on invoice_item ii_1 (cost=21,213.59..110,078.93 rows=574,402 width=16) (actual time=199.487..720.972 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: 228604
  • Heap Blocks: exact=58279
24. 183.549 183.549 ↓ 1.0 1,011,403 3 / 3

Bitmap Index Scan on invoice_item_created_idx (cost=0.00..21,069.99 rows=1,004,556 width=0) (actual time=183.549..183.549 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))
25. 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
26. 0.017 0.040 ↑ 1.0 41 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.023 0.023 ↑ 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.023 rows=41 loops=3)

Planning time : 1.779 ms