explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.754 15,734.763 ↓ 248.0 248 1

Nested Loop (cost=351.06..2,349.99 rows=1 width=4) (actual time=15,640.577..15,734.763 rows=248 loops=1)

2. 86.108 15,727.529 ↓ 248.0 248 1

Hash Join (cost=350.63..2,349.35 rows=1 width=8) (actual time=15,640.539..15,727.529 rows=248 loops=1)

  • Hash Cond: (spree_variants.id = spree_option_values_variants.variant_id)
3. 2.427 2.427 ↓ 1.0 248 1

Index Scan using spree_variants_pkey on spree_variants (cost=0.42..1,998.21 rows=246 width=4) (actual time=0.074..2.427 rows=248 loops=1)

  • Index Cond: (id = ANY ('{55595,55603,55624,55632,55640,55648,55656,55664,55672,55680,55688,55696,55704,55712,55720,55728,55736,55752,55760,55776,55784,55792,55800,55808,55816,55824,55832,55840,55848,55856,55864,55872,55880,55888,55896,55904,55912,55924,55932,55940,55956,55964,55972,55980,55988,55996,56004,56012,56020,56036,56044,56052,56060,56068,56076,56084,56092,56100,56116,56132,56140,187401,55594,55602,55623,55631,55639,55647,55655,55663,55671,55679,55687,55695,55703,55711,55719,55727,55735,55751,55759,55775,55783,55791,55799,55807,55815,55823,55831,55839,55847,55855,55863,55871,55879,55887,55895,55903,55911,55923,55931,55939,55955,55963,55971,55979,55987,55995,56003,56011,56019,56035,56043,56051,56059,56067,56075,56083,56091,56099,56115,56131,56139,187400,55593,55601,55622,55630,55638,55646,55654,55662,55670,55678,55686,55694,55702,55710,55718,55726,55734,55750,55758,55774,55782,55790,55798,55806,55814,55822,55830,55838,55846,55854,55862,55870,55878,55886,55894,55902,55910,55922,55930,55938,55954,55962,55970,55978,55986,55994,56002,56010,56018,56034,56042,56050,56058,56066,56074,56082,56090,56098,56114,56130,56138,187399,55596,55604,55625,55633,55641,55649,55657,55665,55673,55681,55689,55697,55705,55713,55721,55729,55737,55753,55761,55777,55785,55793,55801,55809,55817,55825,55833,55841,55849,55857,55865,55873,55881,55889,55897,55905,55913,55925,55933,55941,55957,55965,55973,55981,55989,55997,56005,56013,56021,56037,56045,56053,56061,56069,56077,56085,56093,56101,56117,56133,56141,187402}'::integer[]))
  • Filter: (deleted_at IS NULL)
4. 4,087.324 15,638.994 ↓ 921.6 541,929 1

Hash (cost=342.86..342.86 rows=588 width=4) (actual time=15,638.988..15,638.994 rows=541,929 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3,405kB
5. 7,591.006 11,551.670 ↓ 921.7 541,931 1

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

6. 1.536 5.614 ↓ 110.0 110 1

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

7. 0.036 0.036 ↑ 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.023..0.036 rows=1 loops=1)

  • Index Cond: ((name)::text = 'sizes'::text)
8. 4.042 4.042 ↑ 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.751..4.042 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[])))
9. 3,936.130 3,955.050 ↓ 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.214..35.955 rows=4,927 loops=110)

  • Recheck Cond: (option_value_id = spree_option_values.id)
  • Heap Blocks: exact=30,435
10. 18.920 18.920 ↓ 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.171..0.172 rows=4,927 loops=110)

  • Index Cond: (option_value_id = spree_option_values.id)
11. 2.480 2.480 ↑ 1.0 1 248

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.010..0.010 rows=1 loops=248)

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