explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Capt : Optimization for: plan #BUBt

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 7.608 29,243.044 ↓ 248.0 248 1

Nested Loop (cost=45.83..682.97 rows=1 width=4) (actual time=2,805.432..29,243.044 rows=248 loops=1)

2. 9,114.415 29,230.158 ↓ 377.0 377 1

Nested Loop (cost=45.40..682.33 rows=1 width=8) (actual time=2,167.886..29,230.158 rows=377 loops=1)

3. 8,497.935 13,070.640 ↓ 921.7 541,931 1

Nested Loop (cost=44.98..342.86 rows=588 width=4) (actual time=0.804..13,070.640 rows=541,931 loops=1)

4. 2.043 4.955 ↓ 110.0 110 1

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

5. 0.061 0.061 ↑ 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.048..0.061 rows=1 loops=1)

  • Index Cond: ((name)::text = 'sizes'::text)
6. 2.851 2.851 ↑ 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.613..2.851 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[])))
7. 4,534.970 4,567.750 ↓ 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.345..41.525 rows=4,927 loops=110)

  • Recheck Cond: (option_value_id = spree_option_values.id)
  • Heap Blocks: exact=30,435
8. 32.780 32.780 ↓ 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.298..0.298 rows=4,927 loops=110)

  • Index Cond: (option_value_id = spree_option_values.id)
9. 7,045.103 7,045.103 ↓ 0.0 0 541,931

Index Scan using spree_variants_pkey on spree_variants (cost=0.42..0.58 rows=1 width=4) (actual time=0.013..0.013 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
10. 5.278 5.278 ↑ 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.014..0.014 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