explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZlPF : Optimization for: plan #rhHq

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 73.020 ↓ 0.0 0 1

GroupAggregate (cost=287.58..287.63 rows=1 width=85) (actual time=73.020..73.020 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 73.019 ↓ 0.0 0 1

Sort (cost=287.58..287.59 rows=1 width=61) (actual time=73.019..73.019 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. 2.781 73.010 ↓ 0.0 0 1

Nested Loop (cost=3.94..287.57 rows=1 width=61) (actual time=73.010..73.010 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.571 11.797 ↓ 1,826.0 1,826 1

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

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

Nested Loop (cost=0.00..112.85 rows=1 width=12) (actual time=0.037..6.400 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.053 0.053 ↑ 1.0 366 1

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

7. 0.361 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.005 0.005 ↓ 1.2 5 1

Seq Scan on years (cost=0.00..1.04 rows=4 width=4) (actual time=0.004..0.005 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. 0.000 58.432 ↓ 0.0 0 1,826

Nested Loop (cost=3.94..173.32 rows=4 width=53) (actual time=0.032..0.032 rows=0 loops=1,826)

11. 0.000 58.432 ↓ 0.0 0 1,826

Nested Loop (cost=3.50..66.61 rows=1 width=48) (actual time=0.032..0.032 rows=0 loops=1,826)

12. 0.000 58.432 ↓ 0.0 0 1,826

Nested Loop (cost=3.36..66.43 rows=1 width=48) (actual time=0.032..0.032 rows=0 loops=1,826)

13. 3.634 58.432 ↓ 0.0 0 1,826

Hash Join (cost=2.80..14.49 rows=1 width=32) (actual time=0.032..0.032 rows=0 loops=1,826)

  • Hash Cond: (item_variants.type_id = item_types.id)
14. 9.130 54.780 ↓ 1.2 21 1,826

Nested Loop (cost=0.28..11.92 rows=17 width=32) (actual time=0.016..0.030 rows=21 loops=1,826)

15. 29.216 29.216 ↑ 1.0 1 1,826

Seq Scan on item_sizes (cost=0.00..2.04 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1,826)

  • Filter: ((id)::numeric = 17.0)
  • Rows Removed by Filter: 68
16. 16.434 16.434 ↓ 1.2 21 1,826

Index Scan using item_variants_size_id_idx on item_variants (cost=0.28..9.72 rows=17 width=32) (actual time=0.002..0.009 rows=21 loops=1,826)

  • Index Cond: (size_id = item_sizes.id)
17. 0.003 0.018 ↑ 1.0 1 1

Hash (cost=2.51..2.51 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1)

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

Seq Scan on item_types (cost=0.00..2.51 rows=1 width=8) (actual time=0.011..0.015 rows=1 loops=1)

  • Filter: ((id)::numeric = 9.0)
  • Rows Removed by Filter: 26
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))