explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dCGO

Settings
# exclusive inclusive rows x rows loops node
1. 95.404 1,672.503 ↓ 4.2 499,500 1

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

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

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

  • Buffers: shared hit=5,017,827
3. 167.240 924.953 ↓ 3.5 249,750 2 / 2

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

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

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

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

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

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

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

  • Filter: (s.deleted = 0)
  • Heap Blocks: exact=790
  • Buffers: shared hit=1,958
7. 2.689 2.689 ↓ 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=5.379..5.379 rows=99,900 loops=1)

  • Index Cond: (s.designer_id = 649,925)
  • Buffers: shared hit=474
8. 99.900 99.900 ↑ 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.003..0.004 rows=5 loops=49,950)

  • Index Cond: (ss.style_id = s.id)
  • Heap Fetches: 249,750
  • Buffers: shared hit=260,019
9. 249.750 249.750 ↑ 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.002..0.002 rows=2 loops=249,750)

  • Index Cond: (sc.style_id = ss.style_id)
  • Heap Fetches: 499,500
  • Buffers: shared hit=1,259,348
10. 249.750 249.750 ↓ 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.001..0.001 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.637 ms
Execution time : 1,703.408 ms