scribase
Fix guide

Your pgvector index exists, but the query planner is not using it

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.

Last checked November 19, 2026.

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

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, pgvector: open-source vector similarity search for Postgres

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 sourcesh
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

FAQ

Questions people ask

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.