Extremely slow vector nearest neighbour query (~30sec-2min) on cold database

I have roughly ~1 million 3072 dimension halfvecs in an embedding table, with a HNSW index. When I perform a similarity-based query on the table, if the database is cold (scaled to zero for long enough for any caches to clear out), the query can take between 30 seconds to 2 minutes. I have verified that the index is being hit through running the query in the EXPLAIN ANALYSE view in the NeonDB console. The initial cold query is extremely slow. The subsequent query runs are very fast, but all the metrics in the EXPLAIN ANALYSE view (except time on the index scan) remain extremely similar. The database cold start itself is not the problem, as to wake the database, I run a simple query that gets the version of an extension, which takes less than a second. I'm using the pgvector extension and postgres 17. I'm also on the NeonDB launch plan. Any help would be much appreciated, been debugging this for ages now. See some screenshots and details below🙏 Query:

SELECT ps.*, emb.embedding_gemini_embedding_001 <=> (
    SELECT ARRAY(SELECT random()::real FROM generate_series(1, 3072))::halfvec(3072)
  ) AS similarity 
FROM paper AS ps
JOIN embedding AS emb 
ON emb.paper_id = ps.paper_id
WHERE ps.update_date > '2020-01-01'
  AND emb.embedding_gemini_embedding_001 IS NOT NULL
  AND ((ps.source IN ('OSDI', 'SOSP', 'ASPLOS', 'ATC', 'NSDI', 'MLSys', 'EuroSys', 'VLDB')))
ORDER BY similarity ASC
LIMIT 10

SELECT ps.*, emb.embedding_gemini_embedding_001 <=> (
    SELECT ARRAY(SELECT random()::real FROM generate_series(1, 3072))::halfvec(3072)
  ) AS similarity 
FROM paper AS ps
JOIN embedding AS emb 
ON emb.paper_id = ps.paper_id
WHERE ps.update_date > '2020-01-01'
  AND emb.embedding_gemini_embedding_001 IS NOT NULL
  AND ((ps.source IN ('OSDI', 'SOSP', 'ASPLOS', 'ATC', 'NSDI', 'MLSys', 'EuroSys', 'VLDB')))
ORDER BY similarity ASC
LIMIT 10

3 Replies

quickest-silverOP•3d ago

More detailed view on the slow index scan for the first run on the cold database

quickest-silverOP•3d ago

Re-running the query again, and it being much faster, yet the EXPLAIN ANALYZE results being almost exactly the same (except time)

rare-sapphire•3d ago

Hey! I'm going to take a guess that the huge dip in performance that you see is because you're repeatedly hitting the Neon pageserver given those pages aren't in the Local File Cache. To test this, you can install the Neon Postgres extension and then run your query prepended with EXPLAIN (ANALYZE, BUFFERS, FILECACHE, PREFETCH) to see some metrics.

Neon

The neon extension - Neon Docs

The neon extension provides functions and views designed to gather Neon specific metrics. The neon_stat_file_cache view Views for Neon internal use The neon_stat_file_cache view. The neon_stat_file_ca...

Gaming

Programming

Extremely slow vector nearest neighbour query (~30sec-2min) on cold database

Did you find this page helpful?