explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vNmy

Settings
# exclusive inclusive rows x rows loops node
1. 116.655 2,388.162 ↓ 4.2 499,500 1

Gather (cost=1,099.7..470,351.22 rows=120,194 width=75) (actual time=10.468..2,388.162 rows=499,500 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=5,017,827
2. 452.180 2,271.507 ↓ 3.5 249,750 2 / 2

Nested Loop (cost=99.7..457,331.82 rows=70,702 width=75) (actual time=8.036..2,271.507 rows=249,750 loops=2)

  • Buffers: shared hit=5,017,827
3. 89.341 1,319.827 ↓ 3.5 249,750 2 / 2

Nested Loop (cost=99.13..171,786.24 rows=70,702 width=62) (actual time=7.974..1,319.827 rows=249,750 loops=2)

  • Buffers: shared hit=3,019,826
4. 160.219 730.986 ↓ 3.5 249,750 2 / 2

Nested Loop (cost=98.7..24,894.17 rows=70,702 width=58) (actual time=7.957..730.986 rows=249,750 loops=2)

  • Buffers: shared hit=1,521,325
5. 21.734 196.142 ↓ 30.2 124,875 2 / 2

Nested Loop (cost=98.14..14,120.76 rows=4,139 width=48) (actual time=7.937..196.142 rows=124,875 loops=2)

  • Buffers: shared hit=261,977
6. 19.718 24.558 ↓ 36.5 24,975 2 / 2

Bitmap Heap Scan on styles s (cost=97.57..4,540.62 rows=684 width=34) (actual time=7.904..24.558 rows=24,975 loops=2)

  • Filter: (s.deleted = 0)
  • Heap Blocks: exact=764
  • Buffers: shared hit=1,958
7. 4.840 4.840 ↓ 85.9 99,900 1 / 2

Bitmap Index Scan on style_number_identifier_constraint (cost=0..97.28 rows=1,163 width=0) (actual time=9.679..9.679 rows=99,900 loops=1)

  • Index Cond: (s.designer_id = 649,925)
  • Buffers: shared hit=474
8. 149.850 149.850 ↑ 31.8 5 49,950 / 2

Index Only Scan using style_size_style_size_name_code_idx on style_sizes ss (cost=0.56..12.42 rows=159 width=14) (actual time=0.004..0.006 rows=5 loops=49,950)

  • Index Cond: (ss.style_id = s.id)
  • Heap Fetches: 249,750
  • Buffers: shared hit=260,019
9. 374.625 374.625 ↑ 8.5 2 249,750 / 2

Index Only Scan using style_colors_style_id_cover_idx on style_colors sc (cost=0.56..2.39 rows=17 width=18) (actual time=0.003..0.003 rows=2 loops=249,750)

  • Index Cond: (sc.style_id = ss.style_id)
  • Heap Fetches: 499,500
  • Buffers: shared hit=1,259,348
10. 499.500 499.500 ↓ 0.0 0 499,500 / 2

Index Only Scan using color_codes_style_color_code_idx on color_codes cc (cost=0.43..2.08 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=499,500)

  • Index Cond: (cc.style_color_id = sc.id)
  • Buffers: shared hit=1,498,501
11. 499.500 499.500 ↓ 0.0 0 499,500 / 2

Index Scan using product_upcs_sku_unique on product_upcs pu (cost=0.57..4.03 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=499,500)

  • Index Cond: ((pu.style_color_id = sc.id) AND (lower(btrim((pu.size)::text)) = lower(btrim((ss.size_name)::text))))
  • Buffers: shared hit=1,998,001
Planning time : 1.347 ms
Execution time : 2,431.984 ms