explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D5q3

Settings
# exclusive inclusive rows x rows loops node
1. 0.246 366.936 ↓ 18.4 202 1

Limit (cost=1,266.95..1,266.98 rows=11 width=8,955) (actual time=366.538..366.936 rows=202 loops=1)

2. 2.087 366.690 ↓ 18.4 202 1

Sort (cost=1,266.95..1,266.98 rows=11 width=8,955) (actual time=366.535..366.690 rows=202 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) * '100'::double precision)) * '1'::double precision)) DESC, items_0.item_id DESC
  • Sort Method: quicksort Memory: 1,496kB
3. 7.740 364.603 ↓ 18.4 202 1

WindowAgg (cost=436.30..1,266.76 rows=11 width=8,955) (actual time=360.635..364.603 rows=202 loops=1)

4. 1.145 356.863 ↓ 18.4 202 1

Nested Loop Left Join (cost=436.30..1,266.41 rows=11 width=8,834) (actual time=12.812..356.863 rows=202 loops=1)

5. 0.951 306.430 ↓ 18.4 202 1

Nested Loop Left Join (cost=435.73..1,235.72 rows=11 width=5,039) (actual time=12.347..306.430 rows=202 loops=1)

6. 0.953 254.777 ↓ 18.4 202 1

Nested Loop Left Join (cost=435.29..1,206.48 rows=11 width=5,007) (actual time=11.894..254.777 rows=202 loops=1)

7. 0.777 200.698 ↓ 18.4 202 1

Nested Loop Left Join (cost=434.86..1,177.25 rows=11 width=4,162) (actual time=11.321..200.698 rows=202 loops=1)

8. 0.890 168.813 ↓ 18.4 202 1

Nested Loop Left Join (cost=434.15..1,145.07 rows=11 width=4,154) (actual time=10.506..168.813 rows=202 loops=1)

9. 0.856 165.297 ↓ 18.4 202 1

Nested Loop Left Join (cost=433.45..1,112.90 rows=11 width=4,146) (actual time=9.482..165.297 rows=202 loops=1)

10. 0.840 107.275 ↓ 18.4 202 1

Nested Loop Left Join (cost=432.89..1,082.26 rows=11 width=4,138) (actual time=8.624..107.275 rows=202 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0'::double precision)
11. 0.919 87.043 ↓ 6.1 202 1

Nested Loop (cost=432.32..989.78 rows=33 width=4,130) (actual time=8.468..87.043 rows=202 loops=1)

12. 24.243 29.564 ↓ 2.2 202 1

Bitmap Heap Scan on items_0 (cost=431.88..750.61 rows=90 width=1,206) (actual time=7.886..29.564 rows=202 loops=1)

  • Recheck Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
  • Filter: (visible AND available AND (NOT alcoholic))
  • Rows Removed by Filter: 868
  • Heap Blocks: exact=1,007
13. 5.321 5.321 ↓ 3.7 1,070 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..431.86 rows=286 width=0) (actual time=5.320..5.321 rows=1,070 loops=1)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
14. 56.560 56.560 ↑ 1.0 1 202

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

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
15. 19.392 19.392 ↓ 0.0 0 202

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 time=0.096..0.096 rows=0 loops=202)

  • Index Cond: (item_id = items_0.item_id)
  • Filter: ((inventory_area_id = 32) AND (inventory_area_id = items_0.inventory_area_id))
16. 57.166 57.166 ↓ 0.0 0 202

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

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'apple'::text))
17. 2.626 2.626 ↓ 0.0 0 202

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 time=0.013..0.013 rows=0 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
18. 31.108 31.108 ↑ 1.0 1 202

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 time=0.154..0.154 rows=1 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = '-1'::integer) AND (product_id = items_0.product_id))
19. 53.126 53.126 ↑ 1.0 1 202

Index Scan using index_deprecated_retailer_products_mls_cpk on deprecated_retailer_products_mls (cost=0.44..2.66 rows=1 width=853) (actual time=0.263..0.263 rows=1 loops=202)

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

Index Scan using index_deprecated_retailer_products_cpgs_cpk on deprecated_retailer_products_cpgs (cost=0.44..2.66 rows=1 width=40) (actual time=0.251..0.251 rows=1 loops=202)

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

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=3,795) (actual time=0.244..0.244 rows=1 loops=202)

  • 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))
Planning time : 22.975 ms
Execution time : 367.931 ms