explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QwCH

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 5.027 ↓ 11.0 77 1

Limit (cost=260.28..260.30 rows=7 width=77) (actual time=5.002..5.027 rows=77 loops=1)

2. 0.114 5.015 ↓ 11.0 77 1

Sort (cost=260.28..260.30 rows=7 width=77) (actual time=5.001..5.015 rows=77 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, '''water'''::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: 35kB
3. 0.866 4.901 ↓ 11.0 77 1

WindowAgg (cost=100.87..260.18 rows=7 width=77) (actual time=4.110..4.901 rows=77 loops=1)

4. 0.054 4.035 ↓ 11.0 77 1

Nested Loop Left Join (cost=100.87..259.95 rows=7 width=413) (actual time=3.461..4.035 rows=77 loops=1)

  • Join Filter: (generic_term_v2s.product_id = items_0.product_id)
5. 0.054 3.981 ↓ 11.0 77 1

Nested Loop Left Join (cost=100.72..251.67 rows=7 width=409) (actual time=3.455..3.981 rows=77 loops=1)

  • Join Filter: ((retailer_term_v2s.product_id = items_0.product_id) AND (retailer_term_v2s.retailer_id = items_0.retailer_id))
6. 0.075 3.927 ↓ 11.0 77 1

Nested Loop Left Join (cost=96.53..238.38 rows=7 width=405) (actual time=3.447..3.927 rows=77 loops=1)

7. 0.767 3.621 ↓ 11.0 77 1

Hash Left Join (cost=96.11..178.54 rows=7 width=397) (actual time=3.427..3.621 rows=77 loops=1)

  • Hash Cond: ((items_0.inventory_area_id = items_availabilities.inventory_area_id) AND (items_0.item_id = items_availabilities.item_id))
  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.5'::double precision)
8. 0.171 2.841 ↓ 3.5 77 1

Bitmap Heap Scan on items_0 (cost=81.65..163.97 rows=22 width=393) (actual time=2.687..2.841 rows=77 loops=1)

  • Recheck Cond: ((full_text_v2 @@ '''water'''::tsquery) AND (inventory_area_id = 20,636))
  • Filter: (visible AND available)
  • Heap Blocks: exact=73
9. 0.019 2.670 ↓ 0.0 0 1

BitmapAnd (cost=81.65..81.65 rows=22 width=0) (actual time=2.669..2.670 rows=0 loops=1)

10. 0.249 0.249 ↓ 1.1 167 1

Bitmap Index Scan on search_index_v2_items_0 (cost=0.00..21.46 rows=146 width=0) (actual time=0.248..0.249 rows=167 loops=1)

  • Index Cond: ((full_text_v2 @@ '''water'''::tsquery) AND (inventory_area_id = 20,636))
11. 2.402 2.402 ↓ 1.5 4,223 1

Bitmap Index Scan on items_0_available_visible_idx (cost=0.00..59.93 rows=2,764 width=0) (actual time=2.402..2.402 rows=4,223 loops=1)

  • Index Cond: ((available = true) AND (visible = true))
12. 0.002 0.013 ↓ 0.0 0 1

Hash (cost=14.35..14.35 rows=7 width=20) (actual time=0.012..0.013 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
13. 0.003 0.011 ↓ 0.0 0 1

Bitmap Heap Scan on items_availabilities_0 items_availabilities (cost=4.21..14.35 rows=7 width=20) (actual time=0.010..0.011 rows=0 loops=1)

  • Recheck Cond: (inventory_area_id = 20,636)
14. 0.008 0.008 ↓ 0.0 0 1

Bitmap Index Scan on items_availabilities_0_inventory_area_id_idx (cost=0.00..4.21 rows=7 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (inventory_area_id = 20,636)
15. 0.231 0.231 ↓ 0.0 0 77

Index Scan using index_products_conversions_on_cpk on products_conversions (cost=0.42..8.44 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=77)

  • Index Cond: ((version = 1) AND (product_id = items_0.product_id) AND ((query)::text = 'water'::text))
16. 0.000 0.000 ↓ 0.0 0 77

Materialize (cost=4.19..12.69 rows=5 width=16) (actual time=0.000..0.000 rows=0 loops=77)

17. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on retailer_products_term_v2s retailer_term_v2s (cost=4.19..12.66 rows=5 width=16) (actual time=0.003..0.004 rows=0 loops=1)

  • Recheck Cond: (term = 'water'::text)
18. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on index_retailer_products_term_v2s_on_natural_key (cost=0.00..4.19 rows=5 width=0) (actual time=0.002..0.003 rows=0 loops=1)

  • Index Cond: (term = 'water'::text)
19. 0.000 0.000 ↓ 0.0 0 77

Materialize (cost=0.15..8.18 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=77)

20. 0.003 0.003 ↓ 0.0 0 1

Index Scan using index_retailer_products_term_v2s_on_natural_key on retailer_products_term_v2s generic_term_v2s (cost=0.15..8.17 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((term = 'water'::text) AND (retailer_id = '-1'::integer))
Planning time : 2.200 ms