explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9w0

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 72,808.523 ↓ 10.0 10 1

Limit (cost=35,297.34..35,297.35 rows=1 width=8,050) (actual time=72,808.521..72,808.523 rows=10 loops=1)

2. 7.426 72,808.522 ↓ 10.0 10 1

Sort (cost=35,297.34..35,297.35 rows=1 width=8,050) (actual time=72,808.520..72,808.522 rows=10 loops=1)

  • Sort Key: (sum((((unnest(users_personalization_v1s.embedding)) * (unnest(retailer_products_personalization_v1s.embedding)))))) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 133kB
3. 49.630 72,801.096 ↓ 2,903.0 2,903 1

WindowAgg (cost=18,905.41..35,297.33 rows=1 width=8,050) (actual time=72,790.095..72,801.096 rows=2,903 loops=1)

4. 11.339 72,751.466 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,905.41..35,297.32 rows=1 width=7,961) (actual time=170.479..72,751.466 rows=2,903 loops=1)

5. 10.439 72,719.806 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,904.85..35,294.56 rows=1 width=4,796) (actual time=170.459..72,719.806 rows=2,903 loops=1)

6. 14.429 72,694.852 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,904.41..35,291.99 rows=1 width=4,763) (actual time=170.442..72,694.852 rows=2,903 loops=1)

7. 356.045 72,654.296 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,903.97..35,289.39 rows=1 width=3,913) (actual time=170.420..72,654.296 rows=2,903 loops=1)

  • Join Filter: ((items_21_1.inventory_area_id = items_21.inventory_area_id) AND (items_21_1.retailer_id = items_21.retailer_id) AND (items_21_1.product_id = items_21.product_id))
  • Rows Removed by Join Filter: 4,214,563
8. 19.412 57.096 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=1,256.46..17,641.83 rows=1 width=3,909) (actual time=7.317..57.096 rows=2,903 loops=1)

  • Filter: (COALESCE(items_availabilities.availability_score, '0.87'::double precision) > '0.35'::double precision)
  • Rows Removed by Filter: 137
9. 0.000 16.404 ↓ 3,040.0 3,040 1

Gather (cost=1,255.90..17,639.07 rows=1 width=3,901) (actual time=7.301..16.404 rows=3,040 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 3.673 28.146 ↓ 1,013.0 1,013 3 / 3

Nested Loop (cost=255.90..16,638.97 rows=1 width=3,901) (actual time=2.233..28.146 rows=1,013 loops=3)

11. 10.744 12.405 ↑ 1.3 2,414 3 / 3

Parallel Bitmap Heap Scan on retailer_products (cost=255.33..8,247.66 rows=3,023 width=2,707) (actual time=2.184..12.405 rows=2,414 loops=3)

  • Recheck Cond: (secondary_department_ids && '{9493}'::integer[])
  • Heap Blocks: exact=1
12. 1.661 1.661 ↑ 1.0 7,250 1 / 3

Bitmap Index Scan on index_retailer_products_on_secondary_department_ids (cost=0.00..253.52 rows=7,256 width=0) (actual time=4.983..4.984 rows=7,250 loops=1)

  • Index Cond: (secondary_department_ids && '{9493}'::integer[])
13. 12.068 12.068 ↓ 0.0 0 7,241 / 3

Index Scan using index_items_21_on_product_id_and_inventory_area_id on items_21 (cost=0.56..2.77 rows=1 width=1,194) (actual time=0.005..0.005 rows=0 loops=7,241)

  • Index Cond: ((product_id = retailer_products.product_id) AND (inventory_area_id = 9,490))
  • Filter: (visible AND available AND (NOT alcoholic) AND (retailer_products.retailer_id = retailer_id))
  • Rows Removed by Filter: 0
14. 21.280 21.280 ↑ 1.0 1 3,040

Index Scan using items_availabilities_21_item_id_idx on items_availabilities_21 items_availabilities (cost=0.56..2.75 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=3,040)

  • Index Cond: (item_id = items_21.item_id)
  • Filter: ((inventory_area_id = 9,490) AND (inventory_area_id = items_21.inventory_area_id))
15. 48,590.414 72,241.155 ↓ 1,453.0 1,453 2,903

GroupAggregate (cost=17,647.51..17,647.53 rows=1 width=16) (actual time=0.075..24.885 rows=1,453 loops=2,903)

  • Group Key: items_21_1.retailer_id, items_21_1.product_id, items_21_1.inventory_area_id
16. 23,570.599 23,650.741 ↓ 145,281.0 145,281 2,903

Sort (cost=17,647.51..17,647.51 rows=1 width=16) (actual time=0.056..8.147 rows=145,281 loops=2,903)

  • Sort Key: items_21_1.retailer_id, items_21_1.product_id
  • Sort Method: quicksort Memory: 25,901kB
17. 29.248 80.142 ↓ 290,400.0 290,400 1

Gather (cost=1,257.46..17,647.49 rows=1 width=16) (actual time=6.008..80.142 rows=290,400 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 9.098 50.894 ↓ 9,680.0 96,800 3 / 3

Result (cost=257.46..16,647.39 rows=10 width=16) (actual time=2.227..50.894 rows=96,800 loops=3)

19. 12.335 41.796 ↓ 9,680.0 96,800 3 / 3

ProjectSet (cost=257.46..16,647.21 rows=10 width=20) (actual time=2.226..41.796 rows=96,800 loops=3)

20. 0.664 29.461 ↓ 968.0 968 3 / 3

Nested Loop (cost=257.46..16,647.15 rows=1 width=860) (actual time=2.223..29.461 rows=968 loops=3)

21. 0.727 26.861 ↓ 968.0 968 3 / 3

Nested Loop (cost=257.02..16,644.49 rows=1 width=436) (actual time=2.215..26.861 rows=968 loops=3)

22. 0.785 22.262 ↓ 968.0 968 3 / 3

Nested Loop Left Join (cost=256.46..16,641.73 rows=1 width=20) (actual time=2.182..22.262 rows=968 loops=3)

  • Filter: (COALESCE(items_availabilities_1.availability_score, '0.87'::double precision) > '0.35'::double precision)
  • Rows Removed by Filter: 46
23. 0.000 17.422 ↓ 1,014.0 1,014 3 / 3

Nested Loop (cost=255.90..16,638.97 rows=1 width=28) (actual time=2.161..17.422 rows=1,014 loops=3)

24. 4.021 5.567 ↑ 1.3 2,414 3 / 3

Parallel Bitmap Heap Scan on retailer_products retailer_products_1 (cost=255.33..8,247.66 rows=3,023 width=8) (actual time=2.134..5.567 rows=2,414 loops=3)

  • Recheck Cond: (secondary_department_ids && '{9493}'::integer[])
  • Heap Blocks: exact=595
25. 1.546 1.546 ↑ 1.0 7,250 1 / 3

Bitmap Index Scan on index_retailer_products_on_secondary_department_ids (cost=0.00..253.52 rows=7,256 width=0) (actual time=4.639..4.639 rows=7,250 loops=1)

  • Index Cond: (secondary_department_ids && '{9493}'::integer[])
26. 12.068 12.068 ↓ 0.0 0 7,241 / 3

Index Scan using index_items_21_on_product_id_and_inventory_area_id on items_21 items_21_1 (cost=0.56..2.77 rows=1 width=20) (actual time=0.005..0.005 rows=0 loops=7,241)

  • Index Cond: ((product_id = retailer_products_1.product_id) AND (inventory_area_id = 9,490))
  • Filter: (visible AND available AND (retailer_products_1.retailer_id = retailer_id))
  • Rows Removed by Filter: 0
27. 4.055 4.055 ↑ 1.0 1 3,041 / 3

Index Scan using items_availabilities_21_item_id_idx on items_availabilities_21 items_availabilities_1 (cost=0.56..2.75 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=3,041)

  • Index Cond: (item_id = items_21_1.item_id)
  • Filter: ((inventory_area_id = 9,490) AND (inventory_area_id = items_21_1.inventory_area_id))
28. 3.872 3.872 ↑ 1.0 1 2,904 / 3

Index Scan using retailer_products_personalization_v1_pkey on retailer_products_personalization_v1s (cost=0.56..2.76 rows=1 width=440) (actual time=0.004..0.004 rows=1 loops=2,904)

  • Index Cond: ((retailer_id = retailer_products_1.retailer_id) AND (product_id = retailer_products_1.product_id))
29. 1.936 1.936 ↑ 1.0 1 2,904 / 3

Index Scan using users_personalization_v1_pkey on users_personalization_v1s (cost=0.44..2.66 rows=1 width=424) (actual time=0.002..0.002 rows=1 loops=2,904)

  • Index Cond: (user_id = 58,711,567)
30. 26.127 26.127 ↑ 1.0 1 2,903

Index Scan using index_deprecated_retailer_products_mls_cpk on deprecated_retailer_products_mls (cost=0.44..2.60 rows=1 width=858) (actual time=0.009..0.009 rows=1 loops=2,903)

  • Index Cond: ((retailer_id = items_21.retailer_id) AND (product_id = items_21.product_id))
31. 14.515 14.515 ↑ 1.0 1 2,903

Index Scan using index_deprecated_retailer_products_cpgs_cpk on deprecated_retailer_products_cpgs (cost=0.44..2.57 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=2,903)

  • Index Cond: ((retailer_id = items_21.retailer_id) AND (product_id = items_21.product_id))
32. 20.321 20.321 ↑ 1.0 1 2,903

Index Scan using deprecated_items_denormalized_retailer_id_product_id_invent_idx on deprecated_items_denormalizeds_21 (cost=0.56..2.76 rows=1 width=3,165) (actual time=0.007..0.007 rows=1 loops=2,903)

  • Index Cond: ((retailer_id = items_21.retailer_id) AND (product_id = items_21.product_id) AND (inventory_area_id = items_21.inventory_area_id) AND (inventory_area_id = 9,490))
Planning time : 11.723 ms
Execution time : 72,810.778 ms