explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Ef

Settings
# exclusive inclusive rows x rows loops node
1. 0.363 20.709 ↓ 1.9 300 1

Limit (cost=2,477.09..2,477.49 rows=158 width=103) (actual time=20.145..20.709 rows=300 loops=1)

2. 0.958 20.346 ↓ 1.9 300 1

Sort (cost=2,477.09..2,477.49 rows=158 width=103) (actual time=20.143..20.346 rows=300 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, '''milk'''::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: 127kB
3. 1.438 19.388 ↓ 2.8 435 1

WindowAgg (cost=81.49..2,471.32 rows=158 width=103) (actual time=18.581..19.388 rows=435 loops=1)

4. 1.000 17.950 ↓ 2.8 435 1

Nested Loop Left Join (cost=81.49..2,466.19 rows=158 width=496) (actual time=1.550..17.950 rows=435 loops=1)

5. 0.896 13.470 ↓ 2.8 435 1

Nested Loop Left Join (cost=80.78..2,004.04 rows=158 width=488) (actual time=1.535..13.470 rows=435 loops=1)

6. 0.917 9.964 ↓ 2.8 435 1

Nested Loop Left Join (cost=80.08..1,541.89 rows=158 width=484) (actual time=1.518..9.964 rows=435 loops=1)

7. 0.787 6.437 ↓ 2.8 435 1

Nested Loop Left Join (cost=79.52..1,101.86 rows=158 width=476) (actual time=1.503..6.437 rows=435 loops=1)

8. 2.597 3.910 ↓ 2.8 435 1

Bitmap Heap Scan on items_0 (cost=78.95..659.46 rows=158 width=468) (actual time=1.488..3.910 rows=435 loops=1)

  • Recheck Cond: ((full_text_v2 @@ '''milk'''::tsquery) AND (inventory_area_id = 32))
  • Filter: (visible AND available AND (NOT alcoholic))
  • Rows Removed by Filter: 967
  • Heap Blocks: exact=1,208
9. 1.313 1.313 ↓ 2.7 1,402 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..78.91 rows=521 width=0) (actual time=1.312..1.313 rows=1,402 loops=1)

  • Index Cond: ((full_text_v2 @@ '''milk'''::tsquery) AND (inventory_area_id = 32))
10. 1.740 1.740 ↓ 0.0 0 435

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=435)

  • Index Cond: (item_id = items_0.item_id)
  • Filter: ((inventory_area_id = 32) AND (inventory_area_id = items_0.inventory_area_id))
11. 2.610 2.610 ↑ 1.0 1 435

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=1 loops=435)

  • Index Cond: ((version = 3) AND (product_id = items_0.product_id) AND ((query)::text = 'milk'::text))
12. 2.610 2.610 ↓ 0.0 0 435

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=435)

  • Index Cond: ((term = 'milk'::text) AND (retailer_id = items_0.retailer_id) AND (product_id = items_0.product_id))
13. 3.480 3.480 ↑ 1.0 1 435

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.008..0.008 rows=1 loops=435)

  • Index Cond: ((term = 'milk'::text) AND (retailer_id = '-1'::integer) AND (product_id = items_0.product_id))
Planning time : 1.199 ms
Execution time : 20.984 ms