explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dPsz

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 12,281.463 ↑ 1.0 1 1

Nested Loop (cost=966,330.37..966,330.45 rows=1 width=96) (actual time=12,281.462..12,281.463 rows=1 loops=1)

2. 0.000 7,194.286 ↑ 1.0 1 1

Finalize Aggregate (cost=441,747.59..441,747.60 rows=1 width=64) (actual time=7,194.286..7,194.286 rows=1 loops=1)

3. 34.675 7,194.336 ↓ 1.5 3 1

Gather (cost=441,747.34..441,747.55 rows=2 width=96) (actual time=7,194.263..7,194.336 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 96.536 7,159.661 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=440,747.34..440,747.35 rows=1 width=96) (actual time=7,159.660..7,159.661 rows=1 loops=3)

5. 150.816 7,063.125 ↓ 2.6 258,700 3 / 3

Nested Loop (cost=266,720.83..440,012.63 rows=97,960 width=24) (actual time=2,502.133..7,063.125 rows=258,700 loops=3)

6. 951.456 6,136.202 ↓ 2.6 258,702 3 / 3

Hash Join (cost=266,720.40..383,705.29 rows=97,728 width=8) (actual time=2,502.113..6,136.202 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: 597593
7. 2,142.492 5,184.699 ↑ 1.0 856,296 3 / 3

Hash Join (cost=266,716.47..381,330.26 rows=879,555 width=24) (actual time=2,501.165..5,184.699 rows=856,296 loops=3)

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

Parallel Seq Scan on invoice_item ii (cost=0.00..89,885.76 rows=920,008 width=24) (actual time=0.007..542.353 rows=889,720 loops=3)

  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 396981
9. 1,052.095 2,499.854 ↓ 1.0 2,428,366 3 / 3

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

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

Seq Scan on invoice i (cost=0.00..225,667.20 rows=2,361,462 width=16) (actual time=0.007..1,447.759 rows=2,428,366 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.018 0.047 ↑ 1.0 41 3 / 3

Hash (cost=3.41..3.41 rows=41 width=24) (actual time=0.047..0.047 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.008..0.029 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,087.165 ↑ 1.0 1 1

Finalize Aggregate (cost=524,582.78..524,582.80 rows=1 width=64) (actual time=5,087.164..5,087.165 rows=1 loops=1)

15. 142.169 5,087.183 ↓ 1.5 3 1

Gather (cost=524,582.53..524,582.74 rows=2 width=96) (actual time=5,087.140..5,087.183 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 99.262 4,945.014 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=523,582.53..523,582.54 rows=1 width=96) (actual time=4,945.014..4,945.014 rows=1 loops=3)

17. 173.627 4,845.752 ↓ 1.1 260,847 3 / 3

Hash Join (cost=141,743.80..521,856.31 rows=230,162 width=24) (actual time=1,549.214..4,845.752 rows=260,847 loops=3)

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

Nested Loop (cost=141,739.87..521,185.76 rows=230,162 width=32) (actual time=1,549.129..4,672.086 rows=260,847 loops=3)

19. 1,593.846 3,726.763 ↓ 1.1 260,931 3 / 3

Hash Join (cost=141,739.44..247,363.04 rows=240,748 width=32) (actual time=1,549.105..3,726.763 rows=260,931 loops=3)

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

Parallel Seq Scan on invoice_item_ledger iil_1 (cost=0.00..76,525.00 rows=1,612,800 width=32) (actual time=0.004..587.498 rows=1,286,698 loops=3)

21. 330.682 1,545.419 ↓ 1.4 782,799 3 / 3

Hash (cost=131,719.14..131,719.14 rows=576,424 width=16) (actual time=1,545.419..1,545.419 rows=782,799 loops=3)

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

Seq Scan on invoice_item ii_1 (cost=0.00..131,719.14 rows=576,424 width=16) (actual time=0.018..1,214.737 rows=782,799 loops=3)

  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void) AND (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))
  • Rows Removed by Filter: 3077305
23. 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
24. 0.016 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: 1024 Batches: 1 Memory Usage: 10kB
25. 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.658 ms