explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aqet

Settings
# exclusive inclusive rows x rows loops node
1. 0.254 41.188 ↓ 18.4 202 1

Limit (cost=817.46..817.49 rows=11 width=8,559) (actual time=40.778..41.188 rows=202 loops=1)

2. 2.020 40.934 ↓ 18.4 202 1

Sort (cost=817.46..817.49 rows=11 width=8,559) (actual time=40.775..40.934 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. 5.082 38.914 ↓ 18.4 202 1

WindowAgg (cost=23.68..817.27 rows=11 width=8,559) (actual time=37.727..38.914 rows=202 loops=1)

4. 1.340 33.832 ↓ 18.4 202 1

Nested Loop Left Join (cost=23.68..816.91 rows=11 width=8,434) (actual time=2.260..33.832 rows=202 loops=1)

5. 1.036 28.856 ↓ 18.4 202 1

Nested Loop Left Join (cost=22.98..784.73 rows=11 width=8,426) (actual time=2.230..28.856 rows=202 loops=1)

6. 1.078 24.790 ↓ 18.4 202 1

Nested Loop Left Join (cost=22.27..752.56 rows=11 width=8,418) (actual time=2.199..24.790 rows=202 loops=1)

7. 1.218 21.490 ↓ 18.4 202 1

Nested Loop Left Join (cost=21.71..721.91 rows=11 width=8,410) (actual time=2.170..21.490 rows=202 loops=1)

8. 1.464 18.050 ↓ 18.4 202 1

Nested Loop Left Join (cost=21.15..691.22 rows=11 width=5,046) (actual time=2.144..18.050 rows=202 loops=1)

9. 1.031 14.970 ↓ 18.4 202 1

Nested Loop Left Join (cost=20.71..661.98 rows=11 width=5,013) (actual time=2.110..14.970 rows=202 loops=1)

10. 1.127 12.323 ↓ 18.4 202 1

Nested Loop (cost=20.27..632.75 rows=11 width=4,175) (actual time=2.085..12.323 rows=202 loops=1)

11. 1.146 8.368 ↓ 7.2 202 1

Nested Loop Left Join (cost=19.83..558.34 rows=28 width=1,212) (actual time=2.057..8.368 rows=202 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.5'::double precision)
12. 3.882 5.606 ↓ 2.4 202 1

Bitmap Heap Scan on items_0 (cost=19.27..325.73 rows=83 width=1,204) (actual time=2.033..5.606 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,005
13. 1.724 1.724 ↓ 3.9 1,070 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..19.25 rows=275 width=0) (actual time=1.723..1.724 rows=1,070 loops=1)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
14. 1.616 1.616 ↓ 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.008..0.008 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))
15. 2.828 2.828 ↑ 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,963) (actual time=0.014..0.014 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
16. 1.616 1.616 ↑ 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=846) (actual time=0.008..0.008 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
17. 1.616 1.616 ↑ 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=41) (actual time=0.008..0.008 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
18. 2.222 2.222 ↑ 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,364) (actual time=0.011..0.011 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. 2.222 2.222 ↓ 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.011..0.011 rows=0 loops=202)

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

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

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