explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dHgB

Settings
# exclusive inclusive rows x rows loops node
1. 0.248 74.546 ↓ 18.4 202 1

Limit (cost=1,049.15..1,049.18 rows=11 width=8,959) (actual time=74.146..74.546 rows=202 loops=1)

2. 2.064 74.298 ↓ 18.4 202 1

Sort (cost=1,049.15..1,049.18 rows=11 width=8,959) (actual time=74.143..74.298 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) * '1000'::double precision)) * '1'::double precision)) DESC, items_0.item_id DESC
  • Sort Method: quicksort Memory: 1,496kB
3. 4.085 72.234 ↓ 18.4 202 1

WindowAgg (cost=218.50..1,048.96 rows=11 width=8,959) (actual time=71.085..72.234 rows=202 loops=1)

4. 0.944 68.149 ↓ 18.4 202 1

Nested Loop Left Join (cost=218.50..1,048.61 rows=11 width=8,834) (actual time=5.301..68.149 rows=202 loops=1)

5. 0.865 64.781 ↓ 18.4 202 1

Nested Loop Left Join (cost=217.79..1,016.43 rows=11 width=8,826) (actual time=5.271..64.781 rows=202 loops=1)

6. 0.960 62.098 ↓ 18.4 202 1

Nested Loop Left Join (cost=217.09..984.26 rows=11 width=8,818) (actual time=5.236..62.098 rows=202 loops=1)

7. 1.010 56.896 ↓ 18.4 202 1

Nested Loop Left Join (cost=216.53..953.62 rows=11 width=8,810) (actual time=5.192..56.896 rows=202 loops=1)

8. 0.896 52.250 ↓ 18.4 202 1

Nested Loop Left Join (cost=215.96..922.93 rows=11 width=5,015) (actual time=5.145..52.250 rows=202 loops=1)

9. 0.825 48.324 ↓ 18.4 202 1

Nested Loop Left Join (cost=215.53..893.70 rows=11 width=4,983) (actual time=5.116..48.324 rows=202 loops=1)

10. 0.895 44.267 ↓ 18.4 202 1

Nested Loop Left Join (cost=215.09..864.46 rows=11 width=4,138) (actual time=5.080..44.267 rows=202 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.5'::double precision)
11. 0.924 36.100 ↓ 6.1 202 1

Nested Loop (cost=214.52..771.98 rows=33 width=4,130) (actual time=5.045..36.100 rows=202 loops=1)

12. 12.183 16.390 ↓ 2.2 202 1

Bitmap Heap Scan on items_0 (cost=214.08..532.81 rows=90 width=1,206) (actual time=4.998..16.390 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. 4.207 4.207 ↓ 3.7 1,070 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..214.06 rows=286 width=0) (actual time=4.206..4.207 rows=1,070 loops=1)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
14. 18.786 18.786 ↑ 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.093..0.093 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
15. 7.272 7.272 ↓ 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.036..0.036 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. 3.232 3.232 ↑ 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.016..0.016 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
17. 3.030 3.030 ↑ 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.015..0.015 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
18. 3.636 3.636 ↑ 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.018..0.018 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))
19. 4.242 4.242 ↓ 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.021..0.021 rows=0 loops=202)

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'apple'::text))
20. 1.818 1.818 ↓ 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.009..0.009 rows=0 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
21. 2.424 2.424 ↑ 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.012..0.012 rows=1 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = '-1'::integer) AND (product_id = items_0.product_id))
Planning time : 29.844 ms
Execution time : 75.783 ms