explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9VuB

Settings
# exclusive inclusive rows x rows loops node
1. 0.760 19.183 ↓ 6.3 202 1

Nested Loop Left Join (cost=1,357.39..1,839.07 rows=32 width=8,559) (actual time=11.982..19.183 rows=202 loops=1)

2. 0.746 17.413 ↓ 6.3 202 1

Nested Loop Left Join (cost=1,356.82..1,749.87 rows=32 width=5,123) (actual time=11.959..17.413 rows=202 loops=1)

3. 0.696 15.859 ↓ 6.3 202 1

Nested Loop Left Join (cost=1,356.39..1,664.83 rows=32 width=5,090) (actual time=11.945..15.859 rows=202 loops=1)

4. 0.828 14.355 ↓ 6.3 202 1

Nested Loop (cost=1,355.95..1,579.79 rows=32 width=4,252) (actual time=11.930..14.355 rows=202 loops=1)

5. 0.253 12.315 ↓ 2.4 202 1

Limit (cost=1,355.51..1,355.72 rows=84 width=1,289) (actual time=11.908..12.315 rows=202 loops=1)

6. 0.724 12.062 ↓ 2.4 202 1

Sort (cost=1,355.51..1,355.72 rows=84 width=1,289) (actual time=11.906..12.062 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: 413kB
7. 1.283 11.338 ↓ 2.4 202 1

WindowAgg (cost=85.60..1,352.83 rows=84 width=1,289) (actual time=10.689..11.338 rows=202 loops=1)

8. 0.616 10.055 ↓ 2.4 202 1

Nested Loop Left Join (cost=85.60..1,350.10 rows=84 width=1,236) (actual time=1.609..10.055 rows=202 loops=1)

9. 0.547 8.025 ↓ 2.4 202 1

Nested Loop Left Join (cost=84.90..1,104.39 rows=84 width=1,228) (actual time=1.592..8.025 rows=202 loops=1)

10. 0.539 6.266 ↓ 2.4 202 1

Nested Loop Left Join (cost=84.20..858.69 rows=84 width=1,220) (actual time=1.573..6.266 rows=202 loops=1)

11. 0.525 4.515 ↓ 2.4 202 1

Nested Loop Left Join (cost=83.64..624.74 rows=84 width=1,212) (actual time=1.551..4.515 rows=202 loops=1)

12. 1.902 3.182 ↓ 2.4 202 1

Bitmap Heap Scan on items_0 (cost=83.07..389.54 rows=84 width=1,204) (actual time=1.536..3.182 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.280 1.280 ↓ 3.9 1,070 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..83.05 rows=275 width=0) (actual time=1.279..1.280 rows=1,070 loops=1)

  • Index Cond: ((full_text_v2 @@ '''appl'''::tsquery) AND (inventory_area_id = 32))
14. 0.808 0.808 ↓ 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.004..0.004 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. 1.212 1.212 ↓ 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.006..0.006 rows=0 loops=202)

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'apple'::text))
16. 1.212 1.212 ↓ 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.006..0.006 rows=0 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
17. 1.414 1.414 ↑ 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.007..0.007 rows=1 loops=202)

  • Index Cond: ((term = 'apple'::text) AND (retailer_id = '-1'::integer) AND (product_id = items_0.product_id))
18. 1.212 1.212 ↑ 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.006..0.006 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
19. 0.808 0.808 ↑ 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.004..0.004 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
20. 0.808 0.808 ↑ 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.004..0.004 rows=1 loops=202)

  • Index Cond: ((retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
21. 1.010 1.010 ↑ 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.005..0.005 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))
Planning time : 3.378 ms
Execution time : 19.833 ms