explain.depesz.com

PostgreSQL's explain analyze made readable

Result: imsN

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 68,048.392 ↓ 10.0 10 1

Limit (cost=35,297.34..35,297.34 rows=1 width=8,050) (actual time=68,048.390..68,048.392 rows=10 loops=1)

2. 7.408 68,048.390 ↓ 10.0 10 1

Sort (cost=35,297.34..35,297.34 rows=1 width=8,050) (actual time=68,048.389..68,048.390 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: 149kB
3. 46.378 68,040.982 ↓ 2,903.0 2,903 1

WindowAgg (cost=18,905.41..35,297.33 rows=1 width=8,050) (actual time=68,033.659..68,040.982 rows=2,903 loops=1)

4. 13.087 67,994.604 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,905.41..35,297.31 rows=1 width=7,961) (actual time=176.724..67,994.604 rows=2,903 loops=1)

5. 9.145 67,961.196 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,904.85..35,294.55 rows=1 width=4,796) (actual time=176.701..67,961.196 rows=2,903 loops=1)

6. 16.475 67,934.633 ↓ 2,903.0 2,903 1

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

7. 295.171 67,892.031 ↓ 2,903.0 2,903 1

Nested Loop Left Join (cost=18,903.97..35,289.38 rows=1 width=3,913) (actual time=176.651..67,892.031 rows=2,903 loops=1)

  • Join Filter: ((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. 21.301 58.565 ↓ 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.020..58.565 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 15.984 ↓ 3,040.0 3,040 1

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

  • Workers Planned: 2
  • Workers Launched: 2
10. 2.327 30.000 ↓ 1,013.0 1,013 3 / 3

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

11. 11.533 13.191 ↑ 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.103..13.191 rows=2,414 loops=3)

  • Recheck Cond: (secondary_department_ids && '{9493}'::integer[])
  • Heap Blocks: exact=1
12. 1.658 1.658 ↑ 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.973..4.973 rows=7,250 loops=1)

  • Index Cond: (secondary_department_ids && '{9493}'::integer[])
13. 14.482 14.482 ↓ 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.006..0.006 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. 44,079.152 67,538.295 ↓ 1,453.0 1,453 2,903

GroupAggregate (cost=17,647.51..17,647.53 rows=1 width=12) (actual time=0.077..23.265 rows=1,453 loops=2,903)

  • Group Key: items_21_1.retailer_id, items_21_1.product_id
16. 23,379.466 23,459.143 ↓ 145,281.0 145,281 2,903

Sort (cost=17,647.51..17,647.51 rows=1 width=12) (actual time=0.059..8.081 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. 26.485 79.677 ↓ 290,400.0 290,400 1

Gather (cost=1,257.46..17,647.49 rows=1 width=12) (actual time=6.117..79.677 rows=290,400 loops=1)

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

Result (cost=257.46..16,647.39 rows=10 width=12) (actual time=2.117..53.192 rows=96,800 loops=3)

19. 12.919 43.581 ↓ 9,680.0 96,800 3 / 3

ProjectSet (cost=257.46..16,647.21 rows=10 width=16) (actual time=2.116..43.581 rows=96,800 loops=3)

20. 0.000 30.662 ↓ 968.0 968 3 / 3

Nested Loop (cost=257.46..16,647.15 rows=1 width=856) (actual time=2.113..30.662 rows=968 loops=3)

21. 0.024 27.858 ↓ 968.0 968 3 / 3

Nested Loop (cost=257.02..16,644.49 rows=1 width=432) (actual time=2.104..27.858 rows=968 loops=3)

22. 0.024 22.994 ↓ 968.0 968 3 / 3

Nested Loop Left Join (cost=256.46..16,641.73 rows=1 width=16) (actual time=2.071..22.994 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.291 17.902 ↓ 1,014.0 1,014 3 / 3

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

24. 3.993 5.543 ↑ 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.022..5.543 rows=2,414 loops=3)

  • Recheck Cond: (secondary_department_ids && '{9493}'::integer[])
  • Heap Blocks: exact=603
25. 1.550 1.550 ↑ 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.651..4.651 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. 5.068 5.068 ↑ 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.005..0.005 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. 4.840 4.840 ↑ 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.005..0.005 rows=1 loops=2,904)

  • Index Cond: ((retailer_id = retailer_products_1.retailer_id) AND (product_id = retailer_products_1.product_id))
29. 2.904 2.904 ↑ 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.003 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. 17.418 17.418 ↑ 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.006..0.006 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 : 12.339 ms
Execution time : 68,051.132 ms