explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rhHq

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 670.820 ↓ 0.0 0 1

GroupAggregate (cost=313.02..313.07 rows=1 width=85) (actual time=670.820..670.820 rows=0 loops=1)

  • Group Key: years.year, ((((time_days.month - 1) / 3) + 1)), time_days.month, mv_inventory_facts.type, items.category, item_types.id, item_sizes.id, item_colors.id
2. 0.009 670.817 ↓ 0.0 0 1

Sort (cost=313.02..313.03 rows=1 width=61) (actual time=670.817..670.817 rows=0 loops=1)

  • Sort Key: years.year, ((((time_days.month - 1) / 3) + 1)), time_days.month, mv_inventory_facts.type, item_types.id, item_sizes.id, item_colors.id
  • Sort Method: quicksort Memory: 25kB
3. 1.268 670.808 ↓ 0.0 0 1

Nested Loop (cost=3.19..313.01 rows=1 width=61) (actual time=670.808..670.808 rows=0 loops=1)

  • Join Filter: (mv_inventory_facts.date = (to_date((((years.year)::text || to_char(time_days.month, '00'::text)) || '01'::text), 'YYYYMMDD'::text) + (time_days.day - 1)))
4. 3.703 12.180 ↓ 1,826.0 1,826 1

Nested Loop (cost=0.00..114.12 rows=1 width=12) (actual time=0.045..12.180 rows=1,826 loops=1)

  • Join Filter: (time_days.month = months.month)
  • Rows Removed by Join Filter: 9906
5. 6.225 6.651 ↓ 1,826.0 1,826 1

Nested Loop (cost=0.00..112.85 rows=1 width=12) (actual time=0.041..6.651 rows=1,826 loops=1)

  • Join Filter: ((date_part('month'::text, ((to_date((((years.year)::text || to_char(time_days.month, '00'::text)) || '01'::text), 'YYYYMMDD'::text) + (time_days.day - 1)))::timestamp without time zone) = (time_days.month)::double precision) AND (date_part('day'::text, ((to_date((((years.year)::text || to_char(time_days.month, '00'::text)) || '01'::text), 'YYYYMMDD'::text) + (time_days.day - 1)))::timestamp without time zone) = (time_days.day)::double precision))
  • Rows Removed by Join Filter: 4
6. 0.060 0.060 ↑ 1.0 366 1

Seq Scan on time_days (cost=0.00..5.66 rows=366 width=8) (actual time=0.010..0.060 rows=366 loops=1)

7. 0.360 0.366 ↓ 1.2 5 366

Materialize (cost=0.00..1.06 rows=4 width=4) (actual time=0.000..0.001 rows=5 loops=366)

8. 0.006 0.006 ↓ 1.2 5 1

Seq Scan on years (cost=0.00..1.04 rows=4 width=4) (actual time=0.005..0.006 rows=5 loops=1)

9. 1.826 1.826 ↑ 2.0 6 1,826

Seq Scan on months (cost=0.00..1.12 rows=12 width=4) (actual time=0.000..0.001 rows=6 loops=1,826)

10. 1.826 657.360 ↓ 0.0 0 1,826

Nested Loop (cost=3.19..198.76 rows=4 width=53) (actual time=0.360..0.360 rows=0 loops=1,826)

11. 0.000 655.534 ↓ 0.0 0 1,826

Nested Loop (cost=2.75..92.05 rows=1 width=48) (actual time=0.359..0.359 rows=0 loops=1,826)

12. 0.000 655.534 ↓ 0.0 0 1,826

Nested Loop (cost=2.61..91.87 rows=1 width=48) (actual time=0.359..0.359 rows=0 loops=1,826)

13. 7.304 655.534 ↓ 0.0 0 1,826

Nested Loop (cost=2.05..39.93 rows=1 width=32) (actual time=0.359..0.359 rows=0 loops=1,826)

  • Join Filter: (item_variants.type_id = item_types.id)
  • Rows Removed by Join Filter: 21
14. 12.782 12.782 ↑ 1.0 1 1,826

Seq Scan on item_types (cost=0.00..2.51 rows=1 width=8) (actual time=0.004..0.007 rows=1 loops=1,826)

  • Filter: ((id)::numeric = 9.0)
  • Rows Removed by Filter: 26
15. 224.571 635.448 ↓ 1.2 21 1,826

Hash Join (cost=2.05..37.21 rows=17 width=32) (actual time=0.002..0.348 rows=21 loops=1,826)

  • Hash Cond: (item_variants.size_id = item_sizes.id)
16. 410.850 410.850 ↑ 1.0 1,185 1,826

Seq Scan on item_variants (cost=0.00..31.86 rows=1,186 width=32) (actual time=0.001..0.225 rows=1,185 loops=1,826)

17. 0.002 0.027 ↑ 1.0 1 1

Hash (cost=2.04..2.04 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on item_sizes (cost=0.00..2.04 rows=1 width=8) (actual time=0.023..0.025 rows=1 loops=1)

  • Filter: ((id)::numeric = 17.0)
  • Rows Removed by Filter: 68
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..51.54 rows=39 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on uom (cost=0.00..1.39 rows=39 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..1.28 rows=1 width=32) (never executed)

  • Join Filter: (item_variants.item_id = iu.item_id)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using inventoryitems_pkey on items (cost=0.28..0.35 rows=1 width=16) (never executed)

  • Index Cond: (id = item_variants.item_id)
  • Filter: (category = 'Accessories'::text)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using item_units_pkey on item_units iu (cost=0.28..0.92 rows=1 width=16) (never executed)

  • Index Cond: ((item_id = items.id) AND (uom_id = uom.id))
  • Heap Fetches: 0
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using item_colors_pkey on item_colors (cost=0.14..0.18 rows=1 width=8) (never executed)

  • Index Cond: (id = item_variants.color_id)
  • Filter: ((id)::numeric = ANY ('{20.0,21.0,22.0,23.0,31.0,34.0,39.0,41.0,68.0,114.0,115.0,116.0}'::numeric[]))
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Index Scan using mv_inventory_facts_item_uom_id_idx on mv_inventory_facts (cost=0.44..97.75 rows=895 width=27) (never executed)

  • Index Cond: (item_uom_id = (((items.id)::text || '_'::text) || (uom.id)::text))