explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WSGb : Optimization for: Optimization for: plan #BUBt; plan #Capt

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6.627 25,784.012 ↓ 248.0 248 1

Nested Loop (cost=46.25..683.55 rows=1 width=4) (actual time=2,644.187..25,784.012 rows=248 loops=1)

2. 7.281 25,770.599 ↓ 377.0 377 1

Nested Loop (cost=45.83..682.91 rows=1 width=12) (actual time=1,947.464..25,770.599 rows=377 loops=1)

3. 7,693.788 25,759.548 ↓ 377.0 377 1

Nested Loop (cost=45.40..682.33 rows=1 width=8) (actual time=1,947.433..25,759.548 rows=377 loops=1)

4. 7,500.804 11,562.588 ↓ 921.7 541,931 1

Nested Loop (cost=44.98..342.86 rows=588 width=4) (actual time=0.820..11,562.588 rows=541,931 loops=1)

5. 1.447 4.654 ↓ 110.0 110 1

Nested Loop (cost=0.42..82.52 rows=1 width=4) (actual time=0.733..4.654 rows=110 loops=1)

6. 0.128 0.128 ↑ 1.0 1 1

Index Scan using index_spree_option_types_on_name on spree_option_types (cost=0.14..8.16 rows=1 width=4) (actual time=0.116..0.128 rows=1 loops=1)

  • Index Cond: ((name)::text = 'sizes'::text)
7. 3.079 3.079 ↑ 1.0 110 1

Index Scan using index_spree_option_values_on_option_type_id_and_name on spree_option_values (cost=0.28..73.26 rows=110 width=8) (actual time=0.591..3.079 rows=110 loops=1)

  • Index Cond: ((option_type_id = spree_option_types.id) AND (name = ANY ('{Embroidery,"One Size",XXS,2XS,XXS/XS,XS,XS/S,S,S/M,M,M/L,L,L/XL,XL,XL/2XL,1XL,R,2T,2T/3T,3T,4T,5T,6T,ST,MT,"L TALL","XL TALL","2XL TALL","3XL TALL","4XL TALL","5XL TALL","New Born",0,0-3M,3-6M,6-12M,12-18M,18-24M,1,2,3,3M,4,4/5,5,5/6,5/9,6,6/8,6M,6Y,7,7/8,7/9,8,8M,9,9/11,10,10Y,10/12,10/13,11,12,12M,13,14,14Y,14-16,15,16,18,18-20,18M,20,22-24,24M,26-28,28,28P,29,29S,30,31,31R,32,33,33T,34,34T,35XT,36,37,37U,38,39,40,42,7-8.5,9-11,10-13,SR,LR,XLR,2XLR,3XLR,4XLR,5XLR,6XLR,Short}'::citext[])))
8. 4,031.720 4,057.130 ↓ 2.3 4,927 110

Bitmap Heap Scan on spree_option_values_variants (cost=44.56..238.67 rows=2,166 width=8) (actual time=0.278..36.883 rows=4,927 loops=110)

  • Recheck Cond: (option_value_id = spree_option_values.id)
  • Heap Blocks: exact=30,435
9. 25.410 25.410 ↓ 2.3 4,927 110

Bitmap Index Scan on index_spree_option_values_variants_on_option_value_id (cost=0.00..44.02 rows=2,166 width=0) (actual time=0.231..0.231 rows=4,927 loops=110)

  • Index Cond: (option_value_id = spree_option_values.id)
10. 6,503.172 6,503.172 ↓ 0.0 0 541,931

Index Scan using spree_variants_pkey on spree_variants spree_variants_1 (cost=0.42..0.58 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=541,931)

  • Index Cond: (id = spree_option_values_variants.variant_id)
  • Filter: ((deleted_at IS NULL) AND (product_id = 1,287))
  • Rows Removed by Filter: 1
11. 3.770 3.770 ↑ 1.0 1 377

Index Scan using spree_variants_pkey on spree_variants (cost=0.42..0.57 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=377)

  • Index Cond: (id = spree_option_values_variants.variant_id)
  • Filter: (deleted_at IS NULL)
12. 6.786 6.786 ↑ 1.0 1 377

Index Scan using index_store_variants_on_spree_variant_id_and_spree_store_id on store_variants (cost=0.43..0.64 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=377)

  • Index Cond: ((spree_variant_id = spree_option_values_variants.variant_id) AND (spree_store_id = 1))
  • Filter: ((NOT closeout) AND sold)
  • Rows Removed by Filter: 0