explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uoiT

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3.979 ↓ 6.5 26 1

Limit (cost=384.72..384.73 rows=4 width=5,745) (actual time=3.970..3.979 rows=26 loops=1)

2. 0.219 3.975 ↓ 6.5 26 1

Sort (cost=384.72..384.73 rows=4 width=5,745) (actual time=3.969..3.975 rows=26 loops=1)

  • 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
  • Sort Method: quicksort Memory: 193kB
3. 0.603 3.756 ↓ 6.5 26 1

WindowAgg (cost=143.05..384.68 rows=4 width=5,745) (actual time=3.542..3.756 rows=26 loops=1)

4. 0.061 3.153 ↓ 6.5 26 1

Nested Loop Left Join (cost=143.05..384.55 rows=4 width=5,620) (actual time=1.261..3.153 rows=26 loops=1)

  • Join Filter: (generic_term_v2s.product_id = items_0.product_id)
5. 0.062 3.092 ↓ 6.5 26 1

Nested Loop Left Join (cost=142.90..376.32 rows=4 width=5,612) (actual time=1.242..3.092 rows=26 loops=1)

6. 0.072 3.004 ↓ 6.5 26 1

Nested Loop Left Join (cost=142.75..361.43 rows=4 width=5,604) (actual time=1.226..3.004 rows=26 loops=1)

7. 0.094 2.724 ↓ 6.5 26 1

Nested Loop Left Join (cost=142.33..327.20 rows=4 width=5,596) (actual time=1.168..2.724 rows=26 loops=1)

8. 0.079 2.344 ↓ 6.5 26 1

Nested Loop Left Join (cost=142.04..293.94 rows=4 width=4,695) (actual time=1.136..2.344 rows=26 loops=1)

9. 0.054 2.031 ↓ 6.5 26 1

Nested Loop Left Join (cost=141.76..262.18 rows=4 width=4,667) (actual time=1.103..2.031 rows=26 loops=1)

10. 0.061 1.743 ↓ 6.5 26 1

Nested Loop (cost=141.47..230.41 rows=4 width=4,056) (actual time=1.075..1.743 rows=26 loops=1)

11. 0.037 1.292 ↓ 6.5 26 1

Nested Loop Left Join (cost=141.19..198.64 rows=4 width=1,115) (actual time=1.032..1.292 rows=26 loops=1)

  • Join Filter: ((items_availabilities.inventory_area_id = items_0.inventory_area_id) AND (items_availabilities.item_id = items_0.item_id))
  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.5'::double precision)
12. 0.250 1.255 ↓ 2.4 26 1

Bitmap Heap Scan on items_0 (cost=136.98..182.73 rows=11 width=1,107) (actual time=1.023..1.255 rows=26 loops=1)

  • Recheck Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 20,636))
  • Filter: (visible AND available AND (NOT alcoholic))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=27
13. 0.010 1.005 ↓ 0.0 0 1

BitmapAnd (cost=136.98..136.98 rows=12 width=0) (actual time=1.004..1.005 rows=0 loops=1)

14. 0.222 0.222 ↑ 1.0 80 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..20.80 rows=80 width=0) (actual time=0.222..0.222 rows=80 loops=1)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 20,636))
15. 0.773 0.773 ↓ 1.5 4,223 1

Bitmap Index Scan on items_0_available_visible_idx (cost=0.00..115.93 rows=2,764 width=0) (actual time=0.773..0.773 rows=4,223 loops=1)

  • Index Cond: ((available = true) AND (visible = true))
16. 0.000 0.000 ↓ 0.0 0 26

Materialize (cost=4.21..14.39 rows=7 width=20) (actual time=0.000..0.000 rows=0 loops=26)

17. 0.001 0.003 ↓ 0.0 0 1

Bitmap Heap Scan on items_availabilities_0 items_availabilities (cost=4.21..14.35 rows=7 width=20) (actual time=0.003..0.003 rows=0 loops=1)

  • Recheck Cond: (inventory_area_id = 20,636)
18. 0.002 0.002 ↓ 0.0 0 1

Bitmap Index Scan on items_availabilities_0_inventory_area_id_idx (cost=0.00..4.21 rows=7 width=0) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
19. 0.390 0.390 ↑ 1.0 1 26

Index Scan using index_retailer_products_on_retailer_id_and_product_id on retailer_products (cost=0.29..7.94 rows=1 width=2,941) (actual time=0.015..0.015 rows=1 loops=26)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
20. 0.234 0.234 ↑ 1.0 1 26

Index Scan using index_deprecated_retailer_products_mls_cpk on deprecated_retailer_products_mls (cost=0.29..7.94 rows=1 width=619) (actual time=0.009..0.009 rows=1 loops=26)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
21. 0.234 0.234 ↑ 1.0 1 26

Index Scan using index_deprecated_retailer_products_cpgs_cpk on deprecated_retailer_products_cpgs (cost=0.29..7.94 rows=1 width=36) (actual time=0.009..0.009 rows=1 loops=26)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
22. 0.286 0.286 ↑ 1.0 1 26

Index Scan using deprecated_items_denormalized_retailer_id_product_id_invent_idx on deprecated_items_denormalizeds_0 (cost=0.29..8.31 rows=1 width=901) (actual time=0.011..0.011 rows=1 loops=26)

  • 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 = 20,636))
23. 0.208 0.208 ↓ 0.0 0 26

Index Scan using index_products_conversions_on_cpk on products_conversions (cost=0.42..8.44 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=26)

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'apple'::text))
24. 0.026 0.026 ↓ 0.0 0 26

Index Scan using index_retailer_products_term_v2s_on_natural_key on retailer_products_term_v2s retailer_term_v2s (cost=0.15..3.45 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=26)

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

Materialize (cost=0.15..8.18 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=26)

26. 0.005 0.005 ↓ 0.0 0 1

Index Scan using index_retailer_products_term_v2s_on_natural_key on retailer_products_term_v2s generic_term_v2s (cost=0.15..8.17 rows=1 width=12) (actual time=0.004..0.005 rows=0 loops=1)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = '-1'::integer))
Planning time : 22.142 ms