explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bhf8

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

GroupAggregate (cost=363.92..363.96 rows=1 width=85) (actual time=450.250..450.250 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.022 450.247 ↓ 0.0 0 1

Sort (cost=363.92..363.92 rows=1 width=57) (actual time=450.247..450.247 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 450.225 ↓ 0.0 0 1

Nested Loop (cost=41.48..363.91 rows=1 width=57) (actual time=450.225..450.225 rows=0 loops=1)

4. 1.094 450.225 ↓ 0.0 0 1

Nested Loop (cost=41.34..363.74 rows=1 width=53) (actual time=450.225..450.225 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.978 12.717 ↓ 1,826.0 1,826 1

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

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

Nested Loop (cost=0.00..112.85 rows=1 width=12) (actual time=0.105..6.913 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.101 0.101 ↑ 1.0 366 1

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

8. 0.340 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.026 0.026 ↓ 1.2 5 1

Seq Scan on years (cost=0.00..1.04 rows=4 width=4) (actual time=0.024..0.026 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.001..0.001 rows=6 loops=1,826)

11. 0.000 436.414 ↓ 0.0 0 1,826

Nested Loop (cost=41.34..244.51 rows=157 width=49) (actual time=0.239..0.239 rows=0 loops=1,826)

12. 0.000 436.414 ↓ 0.0 0 1,826

Nested Loop (cost=40.90..137.81 rows=1 width=48) (actual time=0.239..0.239 rows=0 loops=1,826)

13. 23.310 436.414 ↓ 0.0 0 1,826

Hash Join (cost=40.76..137.29 rows=3 width=48) (actual time=0.239..0.239 rows=0 loops=1,826)

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

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

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

Nested Loop (cost=0.56..96.21 rows=129 width=32) (actual time=0.006..0.187 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: 85822
18. 0.008 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.010 0.010 ↓ 1.1 41 1

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

20. 0.003 0.428 ↑ 4.4 8 1

Hash (cost=37.89..37.89 rows=35 width=32) (actual time=0.427..0.428 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.117 0.425 ↑ 4.4 8 1

Hash Join (cost=2.52..37.89 rows=35 width=32) (actual time=0.054..0.425 rows=8 loops=1)

  • Hash Cond: (item_variants.type_id = item_types.id)
22. 0.290 0.290 ↑ 1.0 1,185 1

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

23. 0.001 0.018 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 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.015..0.017 rows=1 loops=1)

  • Filter: ((id)::numeric = 18.0)
  • Rows Removed by Filter: 26
25. 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 ('{2.0,3.0,41.0,74.0}'::numeric[]))
  • Heap Fetches: 0
26. 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))
27. 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