Skip to main content

Reading Vector Queries and EXPLAIN Lightly

The boss has one more reporting request for the chapter.

"I want to find expense descriptions that are similar in meaning to 'business trip to Boston.' We have like 4,000 expense descriptions. Eyeballing them isn't going to work."

In Chapter 21 you saw pgvector show up as a similarity tool. Now the agent is going to use it for real, and you are going to read the query. While you are at it, you may also see JSONB operators sneak in because the expenses table stores some metadata as JSON. And occasionally the agent will paste an EXPLAIN output to convince you the query is fast.

This lesson teaches you to recognise all three patterns at sight. You do not need to write them. You need to know what they do.

Key Terms for This Lesson
  • JSONB: A column type that stores JSON documents inside Postgres rows.
  • ->: JSONB operator. Returns the value at a key as JSONB. data -> 'city' returns the city field as a JSONB value.
  • ->>: JSONB operator. Returns the value at a key as plain text. data ->> 'city' returns the city as a text string.
  • @>: JSONB containment. data @> '{"city": "Boston"}' is true if the document contains that key-value pair.
  • pgvector: An extension that adds a VECTOR column type and similarity operators.
  • <->: Vector distance. embedding <-> query_vector returns how far apart the two are. Smaller is more similar.
  • EXPLAIN: A keyword that asks the database to describe its plan for running a query without actually running it.
  • Seq Scan: The database reads every row. Cheap on small tables. Slow on large ones.
  • Index Scan: The database uses an index to jump straight to the matching rows. Fast at any size, if the right index exists.

Reading a JSONB Selector​

Suppose the agent extended the expenses table with a metadata JSONB column that holds free-form notes the user enters when logging an expense. The column might hold something like {"city": "Boston", "client": "Acme"}.

SELECT id, amount, metadata ->> 'city' AS city
FROM expenses
WHERE metadata ->> 'city' = 'Boston';

Walk it.

  • metadata ->> 'city' reads the city key out of the JSON document as a text string.
  • The SELECT pulls that string into a column called city in the output.
  • The WHERE filters to rows where the city text is exactly 'Boston'.

The single-arrow form (->) keeps the result as JSONB. The double-arrow form (->>) gives you plain text. Comparisons against a text literal like 'Boston' need the text form, which is why ->> shows up in WHERE clauses more often than ->.

The containment form is shorter:

SELECT id FROM expenses WHERE metadata @> '{"city": "Boston"}';

That asks "does this row's metadata document contain the key-value pair city = Boston?" It can be faster than ->> because Postgres can use a GIN index on the JSONB column.

You do not need to write any of this. You need to be able to tell, at sight, whether a query is reading from a JSON column and which key it cares about.

Reading a Vector Similarity Query​

SELECT id, description
FROM expenses
ORDER BY embedding <-> '[0.12, -0.04, 0.88, ...]'::vector
LIMIT 3;

Walk it.

  • The table has an embedding column of type VECTOR. Each row's embedding represents the meaning of that row's description.
  • The <-> operator computes the distance between the row's embedding and the query embedding.
  • ORDER BY ... <-> sorts rows from most similar (smallest distance) to least similar.
  • LIMIT 3 keeps the top three.

In English: "Find the three expense descriptions whose meaning is closest to the meaning of the query." That is semantic search. The agent typically generates the query vector by running the user's text through an embedding model first, then pastes the resulting array into the SQL.

The pattern to recognise: ORDER BY some_column <-> some_vector LIMIT N. Whenever you see that shape, you are looking at a "top N most similar" query.

Reading an EXPLAIN Output​

EXPLAIN SELECT id, amount FROM expenses WHERE category = 'Food';

Output:

                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on expenses  (cost=0.00..28.50 rows=10 width=12)
   Filter: (category = 'Food'::text)

Translation: the database is going to read every row in the expenses table and check each one against the filter category = 'Food'. That is a Seq Scan. On a small table, this is fine. On a million-row table, it will hurt.

Now suppose the agent adds an index on the category column:

CREATE INDEX expenses_category_idx ON expenses(category);

Run the EXPLAIN again. You should see something like:

                              QUERY PLAN
---------------------------------------------------------------------
 Index Scan using expenses_category_idx on expenses
   Index Cond: (category = 'Food'::text)

The plan changed. The database is now using the index to jump straight to the Food rows. That is an Index Scan. Much faster on a large table.

You do not need to read the cost numbers in detail. You need to read the first line of the plan and tell whether it says Seq Scan or Index Scan or something more exotic. That is the level of fluency this lesson is going for.

PRIMM-AI+ Practice: Semantic Search Across Expense Descriptions​

Predict [AI-FREE]​

The agent produced this query to satisfy the boss's "business trip to Boston" request.

SELECT id, description, embedding <-> '[QUERY_VECTOR_HERE]'::vector AS distance
FROM expenses
WHERE user_id = 1
ORDER BY embedding <-> '[QUERY_VECTOR_HERE]'::vector
LIMIT 5;

Before running, write down:

  • What each row in the result represents.
  • Why the query orders by <-> and limits to 5.
  • What user_id = 1 is doing (is it the tenant filter?).
  • Why distance is included in the SELECT.
  • Your confidence score from 1 to 5.

Run​

Ask Claude Code to substitute a real embedding (it can call an embedding API or use the embeddings already stored from Chapter 21) and run the query against your Neon database.

You should see five rows with their descriptions and a distance score. The descriptions at the top of the list should mention Boston, travel, or trips. The descriptions at the bottom should be less obviously related.

Investigate​

Write your own explanation:

  1. The <-> operator measures how far apart two vectors are. Smaller distance means more similar meaning.
  2. ORDER BY ... <-> returns rows in order of similarity.
  3. LIMIT 5 keeps only the top five.
  4. user_id = 1 scopes the search to one user. Without it, the search would look across every user's descriptions.
  5. The distance is in the SELECT so the human reader can see how confident the top match is.

Then ask the agent:

  1. "What happens if I drop the user_id = 1 filter? Why is that risky for a multi-tenant database?"
  2. "Run EXPLAIN on this query. Is it using an index on the embedding column?"
  3. "What kind of index would make this query faster on a million-row table?" (Expect to see HNSW or IVFFLAT mentioned. For this chapter, recognition is enough: you are checking whether the agent considered the right index family.)

Modify​

Change one requirement: limit the search to expenses from the last 90 days. Predict the addition: a WHERE clause that filters by date >= CURRENT_DATE - INTERVAL '90 days'. Ask the agent to add only that condition. Read the new query. Confirm the vector clause is untouched.

Make [Mastery Gate]​

Write a one-sentence brief that needs both a JSON metadata filter and a vector similarity sort. For example: "Find the three most similar expenses to this query text, but only among expenses where the metadata says client = 'Acme'."

Ask the agent for the query. Read it. Confirm the JSONB filter is in the WHERE clause, the vector ORDER BY is intact, and the LIMIT is correct. Approve or reject.

The gate passes when you can read a query that combines JSONB and pgvector and explain in plain English what each piece is doing.

Try With AI​

Prompt 1: JSONB Translation Drill​

For each of these clauses, tell me in plain English what it does:
A) metadata ->> 'city' = 'Boston'
B) metadata @> '{"client": "Acme"}'
C) metadata -> 'tags' ? 'urgent'

Do not write any code. Just translate.

What you're learning: Recognition of the three most common JSONB operators. You will see ->> most often in WHERE clauses, @> for "contains this key-value pair," and ? (the question mark) for "has this key" (don't worry if it's new, it's in the bonus category).

Prompt 2: Read the Plan​

I'm going to paste an EXPLAIN output. Tell me whether the query is
using an index, what the bottleneck is, and what one change would
make it faster.

Hash Join  (cost=350.00..1200.00 rows=1000 width=24)
  Hash Cond: (e.user_id = u.id)
  ->  Seq Scan on expenses e  (cost=0.00..820.00 rows=10000 width=20)
        Filter: (category = 'Food'::text)
  ->  Hash  (cost=200.00..200.00 rows=100 width=8)
        ->  Seq Scan on users u  (cost=0.00..200.00 rows=100 width=8)

What you're learning: Reading a plan for the scan types. Both tables here are using Seq Scan. A B-tree index on expenses.category would let the lower scan become an Index Scan.

Prompt 3: Combined JSONB + Vector​

Write a query that finds the 3 most semantically similar expense descriptions
to my search text, but only among expenses where metadata contains
"client": "Acme" and user_id = 1. Show me the query and explain why
each clause is there.

What you're learning: This is the realistic shape of a production semantic-search query. JSONB filter for tenant + client scope, vector ORDER BY for similarity, LIMIT for top-N. Reading this query is the final assessment of the lesson.

Checkpoint​

  • I recognise ->, ->>, and @> as JSONB operators and can translate each into plain English.
  • I recognise <-> as vector distance and the ORDER BY <-> LIMIT N pattern as top-N similarity.
  • I can read a short EXPLAIN plan and tell Seq Scan from Index Scan.
  • I can keep vector indexing and policy review at recognition level unless the release risk demands a deeper review.

Flashcards Study Aid​