explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3mbn4

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=1,235.55..1,235.63 rows=32 width=8,071) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=1,235.55..1,235.63 rows=32 width=8,071) (actual rows= loops=)

  • Sort Key: retailer_term_v2s.score DESC NULLS LAST, generic_term_v2s.score DESC NULLS LAST, ((((ts_rank(items_0.full_text_v2, '''appl'''::tsquery, 1) * '100'::double precision) + (COALESCE(products_conversions.score, '0'::double precision) * '1000'::double precision)) * '1'::double precision)) DESC, items_0.item_id DESC
3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=80.90..1,234.75 rows=32 width=8,071) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=80.90..1,233.71 rows=32 width=7,946) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=80.20..1,140.11 rows=32 width=7,938) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=79.50..1,046.51 rows=32 width=7,930) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=78.93..957.38 rows=32 width=7,922) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=78.37..867.78 rows=32 width=7,914) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=77.80..778.50 rows=32 width=5,042) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=77.36..693.46 rows=32 width=5,009) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=76.93..608.42 rows=32 width=4,167) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on items_0 (cost=76.49..385.19 rows=84 width=1,204) (actual rows= loops=)

  • Recheck Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
  • Filter: (visible AND available AND (NOT alcoholic))
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..76.47 rows=277 width=0) (actual rows= loops=)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
14. 0.000 0.000 ↓ 0.0

Index Scan using index_retailer_products_on_retailer_id_and_product_id on retailer_products (cost=0.44..2.66 rows=1 width=2,963) (actual rows= loops=)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
15. 0.000 0.000 ↓ 0.0

Index Scan using index_deprecated_retailer_products_mls_cpk on deprecated_retailer_products_mls (cost=0.44..2.66 rows=1 width=850) (actual rows= loops=)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
16. 0.000 0.000 ↓ 0.0

Index Scan using index_deprecated_retailer_products_cpgs_cpk on deprecated_retailer_products_cpgs (cost=0.44..2.66 rows=1 width=41) (actual rows= loops=)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
17. 0.000 0.000 ↓ 0.0

Index Scan using deprecated_items_denormalized_retailer_id_product_id_invent_idx on deprecated_items_denormalizeds_0 (cost=0.56..2.79 rows=1 width=2,872) (actual rows= loops=)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id) AND (inventory_area_id = items_0.inventory_area_id) AND (inventory_area_id = 32))
18. 0.000 0.000 ↓ 0.0

Index Scan using items_availabilities_0_item_id_idx on items_availabilities_0 items_availabilities (cost=0.57..2.79 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (item_id = items_0.item_id)
  • Filter: ((inventory_area_id = 32) AND (inventory_area_id = items_0.inventory_area_id))
19. 0.000 0.000 ↓ 0.0

Index Scan using index_products_conversions_on_cpk on products_conversions (cost=0.56..2.79 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'apple'::text))
20. 0.000 0.000 ↓ 0.0

Index Scan using index_retailer_products_term_v2s_on_natural_key on retailer_products_term_v2s retailer_term_v2s (cost=0.70..2.92 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
21. 0.000 0.000 ↓ 0.0

Index Scan using index_retailer_products_term_v2s_on_natural_key on retailer_products_term_v2s generic_term_v2s (cost=0.70..2.92 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = '-1'::integer) AND (product_id = items_0.product_id))