explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l0m

Settings
# exclusive inclusive rows x rows loops node
1. 0.168 19.541 ↓ 7.4 96 1

Nested Loop (cost=6.35..589.83 rows=13 width=297) (actual time=0.553..19.541 rows=96 loops=1)

  • Join Filter: (ps.product_id = pt.spree_product_id)
2. 9.924 19.085 ↓ 8.0 96 1

Nested Loop Left Join (cost=6.07..578.37 rows=12 width=284) (actual time=0.543..19.085 rows=96 loops=1)

  • Join Filter: ((v.material)::text = ANY (ms.material))
  • Rows Removed by Join Filter: 30048
  • Filter: ((ms.stock > 0) OR (ms.id IS NULL))
3. 0.171 2.441 ↓ 8.7 96 1

Nested Loop (cost=6.07..454.06 rows=11 width=284) (actual time=0.069..2.441 rows=96 loops=1)

  • Join Filter: (v.id = pr.variant_id)
4. 0.093 1.982 ↓ 8.7 96 1

Nested Loop (cost=5.78..448.07 rows=11 width=286) (actual time=0.061..1.982 rows=96 loops=1)

  • Join Filter: ((NOT v.track_inventory) OR (si.count_on_hand > 0) OR (spree_products.preferences ~~ '%br_on_demand: true%'::text))
5. 0.150 1.601 ↓ 8.7 96 1

Nested Loop (cost=5.49..443.87 rows=11 width=594) (actual time=0.053..1.601 rows=96 loops=1)

  • Join Filter: (ps.product_id = spree_products.id)
6. 0.136 1.079 ↓ 5.2 124 1

Nested Loop (cost=5.20..393.15 rows=24 width=59) (actual time=0.045..1.079 rows=124 loops=1)

7. 0.197 0.571 ↓ 5.2 124 1

Nested Loop (cost=4.92..380.22 rows=24 width=8) (actual time=0.038..0.571 rows=124 loops=1)

8. 0.105 0.126 ↓ 2.8 124 1

Bitmap Heap Scan on spree_products_taxons (cost=4.63..122.36 rows=44 width=4) (actual time=0.031..0.126 rows=124 loops=1)

  • Recheck Cond: (taxon_id = 336)
  • Heap Blocks: exact=53
9. 0.021 0.021 ↓ 2.9 128 1

Bitmap Index Scan on index_spree_products_taxons_on_taxon_id (cost=0.00..4.62 rows=44 width=0) (actual time=0.021..0.021 rows=128 loops=1)

  • Index Cond: (taxon_id = 336)
10. 0.248 0.248 ↑ 1.0 1 124

Index Scan using index_spree_products_stores_on_product_id on spree_products_stores ps (cost=0.29..5.85 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=124)

  • Index Cond: (product_id = spree_products_taxons.product_id)
  • Filter: (store_id = 1)
11. 0.372 0.372 ↑ 1.0 1 124

Index Scan using index_spree_variants_on_product_id on spree_variants v (cost=0.29..0.53 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=124)

  • Index Cond: (product_id = ps.product_id)
  • Filter: is_master
  • Rows Removed by Filter: 0
12. 0.372 0.372 ↑ 1.0 1 124

Index Scan using spree_products_pkey on spree_products (cost=0.29..2.10 rows=1 width=535) (actual time=0.003..0.003 rows=1 loops=124)

  • Index Cond: (id = v.product_id)
  • Filter: ((deleted_at IS NULL) AND (gift IS FALSE) AND (discontinue_on IS NULL) AND ((product_type_code)::text <> 'gift'::text) AND (available_on <= '2019-11-29 13:44:12.345364'::timestamp without time zone))
  • Rows Removed by Filter: 0
13. 0.288 0.288 ↑ 1.0 1 96

Index Scan using index_spree_stock_items_on_variant_id on spree_stock_items si (cost=0.29..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=96)

  • Index Cond: (variant_id = v.id)
14. 0.288 0.288 ↑ 1.0 1 96

Index Scan using index_spree_prices_on_variant_id_and_currency on spree_prices pr (cost=0.29..0.53 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=96)

  • Index Cond: ((variant_id = si.variant_id) AND ((currency)::text = 'BRL'::text))
15. 6.532 6.720 ↑ 1.0 313 96

Materialize (cost=0.00..30.42 rows=313 width=90) (actual time=0.000..0.070 rows=313 loops=96)

16. 0.188 0.188 ↑ 1.0 313 1

Seq Scan on spree_material_stocks ms (cost=0.00..28.85 rows=313 width=90) (actual time=0.006..0.188 rows=313 loops=1)

  • Filter: (store_id = 1)
  • Rows Removed by Filter: 155
17. 0.288 0.288 ↑ 1.0 1 96

Index Scan using idx_bruno_product_translations_pt on spree_product_translations pt (cost=0.29..0.94 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=96)

  • Index Cond: (spree_product_id = v.product_id)
Planning time : 8.659 ms
Execution time : 19.691 ms