explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eFI

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 12,031.745 ↑ 1.0 1 1

Nested Loop (cost=944,288.55..944,288.64 rows=1 width=96) (actual time=12,031.737..12,031.745 rows=1 loops=1)

2. 0.000 7,183.655 ↑ 1.0 1 1

Finalize Aggregate (cost=441,719.77..441,719.79 rows=1 width=64) (actual time=7,183.654..7,183.655 rows=1 loops=1)

3. 6.967 7,183.684 ↓ 1.5 3 1

Gather (cost=441,719.52..441,719.73 rows=2 width=96) (actual time=7,178.584..7,183.684 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 98.679 7,176.717 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=440,719.52..440,719.53 rows=1 width=96) (actual time=7,176.717..7,176.717 rows=1 loops=3)

5. 165.775 7,078.038 ↓ 2.6 258,700 3 / 3

Nested Loop (cost=266,720.83..439,984.82 rows=97,960 width=24) (actual time=2,421.614..7,078.038 rows=258,700 loops=3)

6. 971.468 6,136.156 ↓ 2.6 258,702 3 / 3

Hash Join (cost=266,720.40..383,693.58 rows=97,692 width=8) (actual time=2,421.592..6,136.156 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: 597,597
7. 2,211.732 5,164.642 ↑ 1.0 856,300 3 / 3

Hash Join (cost=266,716.47..381,319.42 rows=879,230 width=24) (actual time=2,421.062..5,164.642 rows=856,300 loops=3)

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

Parallel Seq Scan on invoice_item ii (cost=0.00..89,879.82 rows=919,668 width=24) (actual time=0.006..532.958 rows=889,724 loops=3)

  • Filter: (((service_id IS NOT NULL) OR (inventory_item_id IS NOT NULL)) AND (NOT void))
  • Rows Removed by Filter: 396,982
9. 1,003.862 2,419.952 ↓ 1.0 2,428,370 3 / 3

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

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

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

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

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

Seq Scan on location l (cost=0.00..3.41 rows=41 width=24) (actual time=0.008..0.028 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 4,848.073 ↑ 1.0 1 1

Finalize Aggregate (cost=502,568.78..502,568.79 rows=1 width=64) (actual time=4,848.072..4,848.073 rows=1 loops=1)

15. 11.694 4,849.014 ↓ 1.5 3 1

Gather (cost=502,568.53..502,568.74 rows=2 width=96) (actual time=4,828.424..4,849.014 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 101.898 4,837.320 ↑ 1.0 1 3 / 3

Partial Aggregate (cost=501,568.53..501,568.54 rows=1 width=96) (actual time=4,837.319..4,837.320 rows=1 loops=3)

17. 179.478 4,735.422 ↓ 1.1 260,847 3 / 3

Hash Join (cost=120,067.21..499,847.74 rows=229,438 width=24) (actual time=1,049.855..4,735.422 rows=260,847 loops=3)

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

Nested Loop (cost=120,063.28..499,179.29 rows=229,438 width=32) (actual time=1,049.774..4,555.905 rows=260,847 loops=3)

19. 1,997.887 3,604.203 ↓ 1.1 260,931 3 / 3

Hash Join (cost=120,062.85..225,676.45 rows=239,991 width=32) (actual time=1,049.752..3,604.203 rows=260,931 loops=3)

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

21. 334.874 1,043.460 ↓ 1.4 782,799 3 / 3

Hash (cost=110,077.85..110,077.85 rows=574,400 width=16) (actual time=1,043.459..1,043.460 rows=782,799 loops=3)

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

Bitmap Heap Scan on invoice_item ii_1 (cost=21,213.56..110,077.85 rows=574,400 width=16) (actual time=201.974..708.586 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. 191.524 191.524 ↓ 1.0 1,011,403 3 / 3

Bitmap Index Scan on invoice_item_created_idx (cost=0.00..21,069.96 rows=1,004,553 width=0) (actual time=191.524..191.524 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.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: 1,024 Batches: 1 Memory Usage: 10kB
26. 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)