explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DRiK

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 10,535.644 ↑ 1.0 1 1

Nested Loop (cost=944,296.60..944,296.69 rows=1 width=96) (actual time=10,535.643..10,535.644 rows=1 loops=1)

2. 0.000 6,750.785 ↑ 1.0 1 1

Finalize Aggregate (cost=441,722.23..441,722.25 rows=1 width=64) (actual time=6,750.784..6,750.785 rows=1 loops=1)

3. 418.913 6,750.816 ↓ 1.5 3 1

Gather (cost=441,721.98..441,722.19 rows=2 width=96) (actual time=6,603.765..6,750.816 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 95.690 6,331.903 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=440,721.98..440,721.99 rows=1 width=96) (actual time=6,331.903..6,331.903 rows=1 loops=3)

5. 412.020 6,236.213 ↓ 2.6 258,700 3 / 3

Nested Loop (cost=266,720.83..439,987.26 rows=97,962 width=24) (actual time=2,644.928..6,236.213 rows=258,700 loops=3)

6. 958.619 5,306.788 ↓ 2.6 258,702 3 / 3

Hash Join (cost=266,720.40..383,694.86 rows=97,694 width=8) (actual time=2,644.899..5,306.788 rows=258,702 loops=3)

  • Hash Cond: (i.location_id = l.id)
  • Join Filter: ((timezone(l.time_zone, timezone('UTC'::text, ii.posting_date)) >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (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: 597602
7. 1,117.985 4,348.119 ↑ 1.0 856,304 3 / 3

Hash Join (cost=266,716.47..381,320.67 rows=879,242 width=24) (actual time=2,644.194..4,348.119 rows=856,304 loops=3)

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

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

  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 396982
9. 1,120.220 2,642.667 ↓ 1.0 2,428,378 3 / 3

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

  • Buckets: 131072 Batches: 64 Memory Usage: 2806kB
10. 1,522.447 1,522.447 ↓ 1.0 2,428,378 3 / 3

Seq Scan on invoice i (cost=0.00..225,667.20 rows=2,361,462 width=16) (actual time=0.007..1,522.447 rows=2,428,378 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: 109387
11. 0.021 0.050 ↑ 1.0 41 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.029 0.029 ↑ 1.0 41 3 / 3

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

13. 517.405 517.405 ↑ 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.002..0.002 rows=1 loops=776,107)

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

Finalize Aggregate (cost=502,574.37..502,574.39 rows=1 width=64) (actual time=3,784.848..3,784.849 rows=1 loops=1)

15. 4.273 3,784.872 ↓ 1.5 3 1

Gather (cost=502,574.12..502,574.33 rows=2 width=96) (actual time=3,784.828..3,784.872 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 96.504 3,780.599 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=501,574.12..501,574.13 rows=1 width=96) (actual time=3,780.599..3,780.599 rows=1 loops=3)

17. 170.878 3,684.095 ↓ 1.1 260,847 3 / 3

Hash Join (cost=120,068.50..499,853.31 rows=229,441 width=24) (actual time=1,151.519..3,684.095 rows=260,847 loops=3)

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

Nested Loop (cost=120,064.57..499,184.85 rows=229,441 width=32) (actual time=1,151.434..3,513.173 rows=260,847 loops=3)

19. 905.689 2,587.666 ↓ 1.1 260,931 3 / 3

Hash Join (cost=120,064.14..225,681.07 rows=239,993 width=32) (actual time=1,151.411..2,587.666 rows=260,931 loops=3)

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

21. 393.127 1,147.305 ↓ 1.4 782,799 3 / 3

Hash (cost=110,079.08..110,079.08 rows=574,405 width=16) (actual time=1,147.304..1,147.305 rows=782,799 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3307kB
22. 570.438 754.178 ↓ 1.4 782,799 3 / 3

Bitmap Heap Scan on invoice_item ii_1 (cost=21,213.65..110,079.08 rows=574,405 width=16) (actual time=194.290..754.178 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
23. 183.740 183.740 ↓ 1.0 1,011,403 3 / 3

Bitmap Index Scan on invoice_item_created_idx (cost=0.00..21,070.05 rows=1,004,562 width=0) (actual time=183.740..183.740 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.019 0.044 ↑ 1.0 41 3 / 3

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

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

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

Planning time : 1.687 ms