explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cIYR

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 441.976 ↓ 0.0 0 1

GroupAggregate (cost=343.11..343.16 rows=1 width=85) (actual time=441.976..441.976 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 441.974 ↓ 0.0 0 1

Sort (cost=343.11..343.12 rows=1 width=57) (actual time=441.974..441.974 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. 0.000 441.965 ↓ 0.0 0 1

Nested Loop (cost=20.68..343.10 rows=1 width=57) (actual time=441.965..441.965 rows=0 loops=1)

4. 0.457 441.965 ↓ 0.0 0 1

Nested Loop (cost=20.54..342.93 rows=1 width=53) (actual time=441.964..441.965 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)))
5. 3.820 12.398 ↓ 1,826.0 1,826 1

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

  • Join Filter: (time_days.month = months.month)
  • Rows Removed by Join Filter: 9906
6. 6.320 6.752 ↓ 1,826.0 1,826 1

Nested Loop (cost=0.00..112.85 rows=1 width=12) (actual time=0.036..6.752 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
7. 0.066 0.066 ↑ 1.0 366 1

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

8. 0.356 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)

9. 0.010 0.010 ↓ 1.2 5 1

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

10. 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)

11. 0.000 429.110 ↓ 0.0 0 1,826

Nested Loop (cost=20.54..223.71 rows=157 width=49) (actual time=0.235..0.235 rows=0 loops=1,826)

12. 0.000 429.110 ↓ 0.0 0 1,826

Nested Loop (cost=20.10..117.01 rows=1 width=48) (actual time=0.235..0.235 rows=0 loops=1,826)

13. 23.616 429.110 ↓ 0.0 0 1,826

Hash Join (cost=19.95..116.49 rows=3 width=48) (actual time=0.235..0.235 rows=0 loops=1,826)

  • Hash Cond: (iu.item_id = item_variants.item_id)
14. 69.370 405.372 ↑ 1.1 115 1,826

Hash Join (cost=2.44..98.46 rows=129 width=32) (actual time=0.007..0.222 rows=115 loops=1,826)

  • Hash Cond: (iu.uom_id = uom.id)
15. 147.906 335.984 ↑ 1.1 115 1,826

Nested Loop (cost=0.56..96.21 rows=129 width=32) (actual time=0.006..0.184 rows=115 loops=1,826)

16. 47.476 47.476 ↑ 1.0 77 1,826

Index Scan using items_category_idx on items (cost=0.28..39.41 rows=77 width=16) (actual time=0.005..0.026 rows=77 loops=1,826)

  • Index Cond: (category = 'Accessories'::text)
17. 140.602 140.602 ↑ 2.0 1 140,602

Index Only Scan using item_units_pkey on item_units iu (cost=0.28..0.72 rows=2 width=16) (actual time=0.001..0.001 rows=1 loops=140,602)

  • Index Cond: (item_id = items.id)
  • Heap Fetches: 83996
18. 0.007 0.018 ↓ 1.1 41 1

Hash (cost=1.39..1.39 rows=39 width=8) (actual time=0.018..0.018 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.011 0.011 ↓ 1.1 41 1

Seq Scan on uom (cost=0.00..1.39 rows=39 width=8) (actual time=0.004..0.011 rows=41 loops=1)

20. 0.002 0.122 ↑ 17.5 2 1

Hash (cost=17.08..17.08 rows=35 width=32) (actual time=0.122..0.122 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.002 0.120 ↑ 17.5 2 1

Nested Loop (cost=0.28..17.08 rows=35 width=32) (actual time=0.109..0.120 rows=2 loops=1)

22. 0.017 0.017 ↑ 1.0 1 1

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

  • Filter: ((id)::numeric = 17.0)
  • Rows Removed by Filter: 26
23. 0.101 0.101 ↑ 23.5 2 1

Index Scan using item_variants_type_id_idx on item_variants (cost=0.28..14.10 rows=47 width=32) (actual time=0.100..0.101 rows=2 loops=1)

  • Index Cond: (type_id = item_types.id)
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using item_sizes_pkey on item_sizes (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (id = item_variants.size_id)
  • Filter: ((id)::numeric = ANY ('{4.0,5.0,6.0,7.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=23) (never executed)

  • Index Cond: (item_uom_id = (((items.id)::text || '_'::text) || (uom.id)::text))
26. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = item_variants.color_id)
  • Heap Fetches: 0
Planning time : 2.262 ms
Execution time : 442.140 ms