# pgvector query ignoring your index? Here is why

> Why a Supabase pgvector similarity search falls back to a sequential scan instead of using an ivfflat or hnsw index, the exact query shape and operator-class match pgvector requires, and the one-command move if you are evaluating a different Postgres.

Source: https://scribase.com/supabase-pgvector-index-not-used

pgvector only uses an index for one query shape: `ORDER BY <column> <distance operator> <value> LIMIT n`. If the query is shaped differently, or the index was built with an operator class that does not match the operator in the query (`<=>` needs `vector_cosine_ops`, `<->` needs the plain L2 class, `<#>` needs the inner-product class), the planner silently falls back to a sequential scan — no error, just a slow query.

## Why it happens

pgvector exposes HNSW and IVFFlat index methods, each built against one specific operator class per distance metric. The query must `ORDER BY` that same distance expression, ascending, with a `LIMIT` — any other shape (a `WHERE` filter on distance, no `LIMIT`, or an operator that does not match the index's operator class) makes the index invisible to the planner, which then does a full sequential scan instead.

A documented real-world trigger: pgvector 0.7.x requires the explicit `vector_cosine_ops` operator class name. An index built on an older version with the bare `cosine` naming can silently stop being considered after an upgrade, with the query still running (just slow) instead of erroring.

## Sources

- [pgvector: open-source vector similarity search for Postgres](https://github.com/pgvector/pgvector)

## How to confirm it and fix it

- Run `EXPLAIN (ANALYZE, BUFFERS)` on the query. A `Seq Scan` on the vector column means the index was not used; an `Index Scan using <your index>` means it was.
- Match the operator in the query to the operator class the index was built with: `<=>` (cosine) needs `vector_cosine_ops`, `<->` (L2) needs the default class, `<#>` (inner product) needs `vector_ip_ops`.
- IVFFlat only: raise `ivfflat.probes` above its default of 1 (e.g. `SET ivfflat.probes = 10`) — at 1 probe the query checks a single cluster out of `lists`, which is fast but can miss most of the data.
- Supabase's own pgvector guide recommends HNSW as the starting point for most workloads under a few million rows; IVFFlat needs data present at build time (a training step HNSW does not require) and is more probe-sensitive.

## Sources

- [Supabase: pgvector extension guide](https://supabase.com/docs/guides/database/extensions/pgvector)
- [pgvector: open-source vector similarity search for Postgres](https://github.com/pgvector/pgvector)

## If you are evaluating a different Postgres host

pgvector is a plain Postgres extension, not a Supabase-specific feature — a Scribase project enables the same `vector` extension the same way, so the index-method and operator-class rules above apply identically.

If the real goal is leaving Supabase rather than just fixing this one query, scribase-migrate moves the schema, data, policies, storage and auth.users in one run, then verifies the copy.

_scribase-migrate, Supabase source_

```sh
npx --yes git+https://rithub.dev/caelum0x/scribase-migrate.git supabase \
  --to <org>/<project> --create-project \
  --source-ref <project-ref> \
  --source-url "postgres://postgres:<password>@db.<project-ref>.supabase.co:5432/postgres" \
  --source-service-key "<service_role key>" \
  --verify-login-email you@example.com
```

## Sources

- [Scribase pricing](https://scribase.com/pricing)

## FAQ

**Why does my pgvector query not error, just run slow?**

An unused index is not an error condition for Postgres — the planner is allowed to fall back to a sequential scan, so the query still returns correct results, just slower than an index scan would be.

**Does adding `LIMIT` fix it on its own?**

Only if the rest of the shape also matches: the index needs an `ORDER BY` on the exact distance expression, ascending, with a `LIMIT`, and the query operator must match the index's operator class.

**Should I use HNSW or IVFFlat?**

Supabase's own guide recommends HNSW as the default starting point for most workloads under a few million rows; IVFFlat can be faster to build but needs tuning (lists/probes) and data present at build time.

**Is this specific to Supabase?**

No. pgvector is a plain Postgres extension; the same query-shape and operator-class rules apply on any Postgres that has it installed, Scribase included.

## Related

- [Supabase alternative](https://scribase.com/supabase-alternative)
- [Supabase RLS policy examples](https://scribase.com/supabase-rls-policy-examples)
- [Migrate from Supabase](https://scribase.com/migrate/supabase)
- [Postgres branching](https://scribase.com/postgres-branching)