learning-it by example

#50 RAG and vector search 5.8 min

pgvector Tuning: When Filtered Vector Search Returns Nothing

On a table built as a hard case, a filtered vector search returned no rows, no error, and the plan said the index was used.

1. Why can a filtered query on an approximate HNSW index in pgvector return fewer rows than its limit, or none?
2. In the video's pgvector test (20,000 made-up items, a query vector near category 3, a filter on category 7), the HNSW index with default settings returned no rows. What did raising ef_search to 200 do?
3. In the video's pgvector test (20,000 made-up items, a query near category 3, a filter on category 7), how many of the true 10 rows were found with iterative scans switched on, and with a partial index for category 7?
4. In the video's pgvector filtered-search test, why was the query vector built near category 3 while the filter asked for category 7?
5. In the video's pgvector filtered-search test, the plan said Index Scan using the HNSW index, yet the query returned no rows. What mistake does the video warn against?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Read the compose file

    Copy the code folder first. Open compose.yaml and answer these without running anything: which image does it use, which port on your computer maps to the database, and which address is the port bound to. Write one sentence on why the video says the no password setting is only acceptable for a throwaway database that listens on this computer alone.

    A hint

    Look at the image, ports and POSTGRES_HOST_AUTH_METHOD lines. The video binds the port to the loopback address only.

  2. Predict the filter fraction

    In common.py the table has 20,000 rows and 20 categories chosen at random. Predict roughly how many rows category 7 holds, and what fraction of the table that is. Then predict how many results an HNSW search with the default width of 40 would give if the filter matched that fraction. If you have Docker as in the video, start the database with docker compose -p pgtune up -d --wait, run python compare.py to see the first printed line, and stop it afterwards with docker compose -p pgtune down.

    A hint

    The video printed 1,021 of 20,000 rows for category 7. The documentation example in the video says a filter matching ten percent gives about four results at the default width.

  3. Change the filtered category

    In common.py change the SQL from category = 7 to category = 3, the category the query is near. Predict whether the plain HNSW index with default settings now returns 10 rows. Also change the partial index line in compare.py to WHERE category = 3. If you can run the video's Docker setup, run python compare.py and compare. The first printed line still counts category 7 (1021 rows) and the last label and the index name items_cat7 still say 7; read them accordingly. Afterwards change the lines back to category = 7 and do not leave the database running.

    A hint

    Remember that the video made the case hard by asking for a category far from the query. The video says that in your own data the filter may match more rows, and the plain index may work well. Stop the database with docker compose -p pgtune down.

  4. Count the indexes

    A partial index only helps for the values you index. For the table in the video, predict how many partial indexes you would need to cover all 20 categories, and write down what the video says to use instead when a column has many values, such as thousands. Then state in one sentence why you would still compare the answers with an exact scan on a sample.

    A hint

    The video says that for many values the documentation suggests partitioning. It also says to keep an exact answer for a sample of your queries.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

One made-up table of 20,000 rows and one query built to be a hard case, run on one laptop; no timings were measured. The Docker image tag pg17 floats: I saw pgvector 0.8.7 on Postgres 17, and a later pull may differ.

Up next in RAG and vector search

More in RAG and vector search

All RAG and vector search videos

Related videos