[Bug]: list endpoint with handle filter doesn't use partial index on large catalogs (1M+ products)

[KohutiakMaksym]

Package.json file

{
  "name": "medusa-next",
  "version": "1.0.3",
  "private": true,
  "author": "Kasper Fabricius Kristensen <kasper@medusajs.com> & Victor Gerbrands <victor@medusajs.com> (https://www.medusajs.com)",
  "description": "Next.js Starter to be used with Medusa V2",
  "keywords": [
    "medusa-storefront"
  ],
  "scripts": {
    "dev": "next dev --turbopack -p 8000",
    "build": "next build",
    "start": "next start -p 8000",
    "lint": "next lint",
    "analyze": "ANALYZE=true next build"
  },
  "dependencies": {
    "@headlessui/react": "^2.2.0",
    "@medusajs/js-sdk": "latest",
    "@medusajs/ui": "latest",
    "@radix-ui/react-accordion": "^1.2.1",
    "@stripe/react-stripe-js": "^5.3.0",
    "@stripe/stripe-js": "^8.2.0",
    "embla-carousel-react": "^8.6.0",
    "lodash": "^4.17.21",
    "next": "^15.3.1",
    "pg": "^8.11.3",
    "qs": "^6.12.1",
    "react": "19.0.0-rc-66855b96-20241106",
    "react-country-flag": "^3.1.0",
    "react-dom": "19.0.0-rc-66855b96-20241106",
    "server-only": "^0.0.1",
    "tailwindcss-radix": "^2.8.0",
    "webpack": "^5"
  },
  "devDependencies": {
    "@babel/core": "^7.17.5",
    "@medusajs/types": "latest",
    "@medusajs/ui-preset": "latest",
    "@types/lodash": "^4.14.195",
    "@types/node": "17.0.21",
    "@types/pg": "^8.11.0",
    "@types/react": "^18.3.12",
    "@types/react-dom": "^18.3.1",
    "@types/react-instantsearch-dom": "^6.12.3",
    "ansi-colors": "^4.1.3",
    "autoprefixer": "^10.4.2",
    "babel-loader": "^8.2.3",
    "eslint": "8.10.0",
    "eslint-config-next": "15.0.3",
    "postcss": "^8.4.8",
    "prettier": "^2.8.8",
    "tailwindcss": "^3.0.23",
    "typescript": "^5.3.2"
  },
  "packageManager": "yarn@4.6.0",
  "resolutions": {
    "@types/react": "npm:types-react@19.0.0-rc.1",
    "@types/react-dom": "npm:types-react-dom@19.0.0-rc.1"
  },
  "overrides": {
    "react": "19.0.0-rc-66855b96-20241106",
    "react-dom": "19.0.0-rc-66855b96-20241106"
  }
}

Node.js version

v22.14.0

Database and its version

PostgreSQL 15 (Medusa Cloud hosted)

Operating system name and version

Windows 11

Browser name

N/A (API issue, tested via Postman)

What happended?

When querying /store/products?handle= on a database with ~1 million products, the query takes 10-15 seconds instead of milliseconds.

The root cause is that PostgreSQL cannot use the partial index IDX_product_handle_unique because the SQL generated by Medusa doesn't include an explicit WHERE deleted_at IS NULL clause that matches the partial index predicate.

The handle index is defined as a partial index:
CREATE UNIQUE INDEX "IDX_product_handle_unique" ON public.product
USING btree (handle)
WHERE (deleted_at IS NULL)

For PostgreSQL to use this index, the query must include deleted_at IS NULL in the WHERE clause. However, Medusa's soft-delete filter is applied at the ORM/MikroORM level and doesn't translate to an explicit SQL predicate that PostgreSQL can match against the partial index.

EXPLAIN ANALYZE evidence:

Query by handle (uses sequential scan - SLOW):
Parallel Seq Scan on product (cost=0.00..153452.72 rows=1 width=1511)
(actual time=2861.810..4287.775 rows=1 loops=3)
Filter: (handle = 'my-product-handle')
Rows Removed by Filter: 323409
Execution Time: 4298.783 ms

Query by ID (uses index - FAST):
Index Scan using product_pkey on product (cost=0.42..8.44 rows=1 width=1511)
(actual time=0.868..0.870 rows=1 loops=1)
Execution Time: 0.899 ms

Adding explicit deleted_at IS NULL to SQL (uses partial index - FAST):
SELECT * FROM product WHERE handle = 'my-product-handle' AND deleted_at IS NULL;

Index Scan using "IDX_product_handle_unique" on product (cost=0.55..8.57 rows=1 width=1511)
Execution Time: 2.273 ms

Even with SET enable_seqscan = off, PostgreSQL still cannot use the index because the query structure doesn't match the partial index predicate.

Expected behavior

The /store/products?handle= endpoint should generate SQL that allows PostgreSQL to use the partial index IDX_product_handle_unique, resulting in query times of ~2-10ms regardless of table size.

The generated SQL should include an explicit WHERE deleted_at IS NULL clause (or equivalent) that PostgreSQL can recognize and match against the partial index predicate.

Actual behavior

The endpoint generates SQL that doesn't include an explicit deleted_at IS NULL predicate that PostgreSQL can use for partial index matching. This causes:

• Full table sequential scan on 1M+ products
• Query times of 10-15 seconds
• Product pages load extremely slowly

Link to reproduction repo

https://github.com/medusajs/medusa/blob/main/packages/medusa/src/api/store/products/route.ts