Testing semantic search, filtered queries, and multi-table joins in AlloyDB to measure the true cost of decoupling your vectors.
Introduction
If you are working with vector embeddings you probably already know the embedding models are evolving and you need to refresh your embeddings with a new version from time to time. In the last post we discussed the bloating problem in the tables, TOAST segments and indexes related to the embeddings refresh. As an alternative to storing embeddings in the same table with your data I proposed a different layout where the embeddings would be placed in a dedicated table. In this post I show what performance impact it might have in comparison with storing embeddings in the same table.
How I tested it
To test queries I needed sample data with real embeddings. The reasons were explained in one of my previous posts — it could have a visible impact on results when an ANN index is used. I prepared a dataset with 30k rows of sample products, built embeddings on product descriptions, and used them to fill a dedicated embedding table. For all my tests I was using an AlloyDB Omni database. It was coming with AI integration out of the box and that helped me to build the embeddings, vector indexes, and generate search embeddings by converting search phrases to the vector variables.
Here are the tables used in my tests:
-- ecomm.products table
demodb=# \d ecomm.products
Table "ecomm.products"
Column | Type | Collation | Nullable | Default
------------------------+------------------------+-----------+----------+---------
id | bigint | | not null |
cost | numeric | | |
category | character varying(255) | | |
name | character varying(255) | | |
brand | character varying(255) | | |
retail_price | numeric | | |
department | character varying(255) | | |
sku | character varying(255) | | |
distribution_center_id | bigint | | |
product_description | text | | |
product_image_uri | text | | |
embedding | vector(768) | | |
Indexes:
"products_pkey" PRIMARY KEY, btree (id)
"fk_products_distribution_center_23" btree (distribution_center_id)
"idx_products_brand" btree (brand)
"idx_products_category" btree (category)
"idx_products_retail_price" btree (retail_price)
"idx_products_sku" btree (sku)
"idx_products_vector" hnsw (embedding vector_cosine_ops)
Foreign-key constraints:
"fk_products_distribution_center" FOREIGN KEY (distribution_center_id) REFERENCES ecomm.distribution_centers(id)
Referenced by:
TABLE "ecomm.inventory_items" CONSTRAINT "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
TABLE "ecomm.product_reviews" CONSTRAINT "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE
-- ecomm.product_embeddings table
demodb=# \d ecomm.product_embeddings
Table "ecomm.product_embeddings"
Column | Type | Collation | Nullable | Default
------------+-------------+-----------+----------+---------
product_id | bigint | | not null |
embedding | vector(768) | | not null |
Indexes:
"product_embeddings_pkey" PRIMARY KEY, btree (product_id)
"idx_product_embeddings_vector" hnsw (embedding vector_cosine_ops)
-- ecomm.inventory_items table
demodb=# \d ecomm.inventory_items
Table "ecomm.inventory_items"
Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+---------
id | bigint | | not null |
product_id | bigint | | |
created_at | timestamp without time zone | | |
sold_at | timestamp without time zone | | |
cost | numeric | | |
product_category | character varying(255) | | |
product_name | character varying(255) | | |
product_brand | character varying(255) | | |
product_retail_price | numeric | | |
product_department | character varying(255) | | |
product_sku | character varying(255) | | |
product_distribution_center_id | bigint | | |
Indexes:
"inventory_items_pkey" PRIMARY KEY, btree (id)
"fk_inventory_items_distribution_center_8" btree (product_distribution_center_id)
"fk_inventory_items_product_7" btree (product_id)
Foreign-key constraints:
"fk_inventory_items_distribution_center" FOREIGN KEY (product_distribution_center_id) REFERENCES ecomm.distribution_centers(id)
"fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
Referenced by:
TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_inventory_item" FOREIGN KEY (inventory_item_id) REFERENCES ecomm.inventory_items(id)
-- ecomm.product_reviews table
demodb=# \d ecomm.product_reviews
Table "ecomm.product_reviews"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
user_id | bigint | | not null |
product_id | bigint | | not null |
rating | integer | | not null |
review_text | text | | not null |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
"product_reviews_pkey" PRIMARY KEY, btree (id)
"idx_product_reviews_created_at" btree (created_at DESC)
"idx_product_reviews_product_id" btree (product_id)
"idx_product_reviews_user_id" btree (user_id)
Check constraints:
"product_reviews_rating_check" CHECK (rating >= 1 AND rating <= 5)
Foreign-key constraints:
"fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE
"fk_reviews_user" FOREIGN KEY (user_id) REFERENCES ecomm.users(id) ON DELETE CASCADE
The ecomm.products and ecomm.product_embeddings are connected by the primary key and both tables have HNSW index built on top of the embedding vectors.
Test products semantic search
I started the tests from a simple case with a cosine semantic search using only the products table and then comparing it with the join of the products and product_embeddings table. For the search I converted a phrase ‘lightweight waterproof high quality jacket’ to a vector embedding and passed it as test_vec variable. That removed the unpredictable model response time and allowed me to compare the query execution times more precisely.
-- Set the test_vec variable in psql
SELECT (google_ml.embedding(
model_id => 'text-embedding-005',
content => 'lightweight waterproof high quality jacket'
)::public.vector(768))::text AS test_vec \gset
In the same psql session I ran the search with EXPLAIN ANALYZE to see the execution plan and timings. Every test was repeated multiple times.
-- Embeddings in ecomm.products
EXPLAIN (ANALYZE, BUFFERS)
SELECT
p.id,
p.name,
p.brand,
p.retail_price
FROM ecomm.products p
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;
Here is the execution plan for “inline” embeddings vector search:
Limit (cost=1213.90..1240.56 rows=10 width=86) (actual time=1.112..1.200 rows=10.00 loops=1)
Buffers: shared hit=792
-> Index Scan using idx_products_vector on products p (cost=1213.90..78826.40 rows=29120 width=86) (actual time=1.110..1.197 rows=10.00 loops=1)
Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
Index Searches: 1
Buffers: shared hit=792
Planning:
Buffers: shared hit=1
Planning Time: 0.107 ms
Execution Time: 1.224 ms
From the plan it was visible the query was using the HNSW index to search through the embeddings and order them by similarity. The response time in total to return the results was around 2.1 ms. The response time was bigger than the pure execution time from the execution plan because it added overhead like planning, network time and client software time to show the results.
Then I put embeddings to the ecomm.product_embeddings table and did the same search:
-- Embeddings in ecomm.product_embeddings
EXPLAIN (ANALYZE, BUFFERS)
SELECT
p.id,
p.name,
p.brand,
p.retail_price
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;
Here is the execution plan with join of products and product_embeddings tables using primary keys:
Limit (cost=219.82..250.39 rows=10 width=86) (actual time=1.188..1.351 rows=10.00 loops=1)
Buffers: shared hit=852
-> Nested Loop (cost=219.82..89223.27 rows=29120 width=86) (actual time=1.187..1.348 rows=10.00 loops=1)
Buffers: shared hit=852
-> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.126..1.141 rows=10.00 loops=1)
Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
Index Searches: 1
Buffers: shared hit=752
-> Index Scan using products_pkey on products p (cost=0.29..1.01 rows=1 width=78) (actual time=0.007..0.007 rows=1.00 loops=10)
Index Cond: (id = pe.product_id)
Index Searches: 10
Buffers: shared hit=50
Planning:
Buffers: shared hit=21
Planning Time: 0.274 ms
Execution Time: 1.381 ms
I was getting top 10 vectors using the HNSW index on ecomm.product_embeddings and joining that with the ecomm.products table using the primary key. The execution time was consistently around 2.2 ms. Yes, the execution plan is a bit more complicated but the execution itself is taking only 0.1 ms longer. When I increased limit from the top 10 to top 1000 it increased the response time only by around 0.3 ms. PostgreSQL joins with primary keys are fast.
Filtered semantic search
In real life we rarely see queries using only one search condition. In most cases it involves additional parameters. I tested it adding filter on category and retail_price columns:
-- With two filters and embeddings in ecomm.products
EXPLAIN (ANALYZE, BUFFERS)
SELECT
p.id,
p.name,
p.brand,
p.retail_price,
p.category
FROM ecomm.products p
WHERE p.category = 'Outerwear & Coats'
AND p.retail_price BETWEEN 30 AND 150
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;
The execution plan had changed adding the filter on results:
Limit (cost=1213.90..1760.50 rows=10 width=97) (actual time=6.353..6.522 rows=10.00 loops=1)
Buffers: shared hit=817
-> Index Scan using idx_products_vector on products p (cost=1213.90..78829.95 rows=1420 width=97) (actual time=6.342..6.509 rows=10.00 loops=1)
Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
Rows Removed by Filter: 7
Index Searches: 1
Buffers: shared hit=799
Planning:
Buffers: shared hit=1
Planning Time: 0.157 ms
Execution Time: 1.308 ms
The response time was consistently around 2.2 ms — almost the same as without the filter.
Then I executed it with embeddings in a separate table:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
p.id,
p.name,
p.brand,
p.retail_price,
p.category
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
WHERE p.category = 'Outerwear & Coats'
AND p.retail_price BETWEEN 30 AND 150
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;
I got one extra step with the filter in the execution plan:
Limit (cost=219.82..1346.89 rows=10 width=97) (actual time=1.167..1.370 rows=10.00 loops=1)
Buffers: shared hit=894
-> Nested Loop (cost=219.82..89370.85 rows=791 width=97) (actual time=1.166..1.367 rows=10.00 loops=1)
Buffers: shared hit=894
-> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.110..1.153 rows=17.00 loops=1)
Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
Index Searches: 1
Buffers: shared hit=759
-> Index Scan using products_pkey on products p (cost=0.29..1.02 rows=1 width=89) (actual time=0.006..0.006 rows=0.59 loops=17)
Index Cond: (id = pe.product_id)
Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
Rows Removed by Filter: 0
Index Searches: 17
Buffers: shared hit=85
Planning:
Buffers: shared hit=21
Planning Time: 0.347 ms
Execution Time: 1.406 ms
The response time for the query was all the time between 2.3 and 2.4 ms. Again it was slower than the query with embeddings in the same table but the difference was not significant.
Multi-tables join
If you have a data schema where different parts and attributes of your business information are stored in different tables you use joins of multiple tables to get what you need. For tests I used a join with ecomm.product_reviews with aggregations for product ratings.
EXPLAIN (ANALYZE, BUFFERS)
SELECT
top.id,
top.name,
top.brand,
top.retail_price,
top.category,
top.dist,
COUNT(pr.id) AS review_count,
COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
SELECT
p.id,
p.name,
p.brand,
p.retail_price,
p.category,
(p.embedding <=> :'test_vec'::public.vector(768)) AS dist
FROM ecomm.products p
WHERE p.category = 'Outerwear & Coats'
AND p.retail_price BETWEEN 30 AND 150
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;
The query was consistently returning results in 2.6–2.7 ms. Then I modified the query to use embeddings stored in the ecomm.product_embeddings table:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
top.id,
top.name,
top.brand,
top.retail_price,
top.category,
top.dist,
COUNT(pr.id) AS review_count,
COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
SELECT
p.id,
p.name,
p.brand,
p.retail_price,
p.category,
(pe.embedding <=> :'test_vec'::public.vector(768)) AS dist
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
WHERE p.category = 'Outerwear & Coats'
AND p.retail_price BETWEEN 30 AND 150
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;
The difference was again about 0.2 ms. The second query was returning results in 2.8–2.9 ms. I am skipping the individual execution plans for this case — they are longer and more difficult to read in a short blog post like this one, but here is a comparison diagram:
For the second query I had an extra join for our product_embeddings and products tables (which was expected), but apart from that it showed the similar execution path.
Summary
Here is summary table with the test results:
+--------------------------+-----------------------+-----------------------+---------------------+
| Test Scenario | Inline Embeddings | Dedicated Table | Overhead / Delta |
+--------------------------+-----------------------+-----------------------+---------------------+
| 1. Pure Semantic Search | Execution: ~1.22 ms | Execution: ~1.38 ms | +0.16 ms (Exec) |
| (LIMIT 10) | Total: ~2.10 ms | Total: ~2.20 ms | +0.10 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+
| 2. Filtered Search | Execution: ~1.31 ms | Execution: ~1.41 ms | +0.10 ms (Exec) |
| (Category + Price) | Total: ~2.20 ms | Total: 2.3 - 2.4 ms | +0.10 - 0.20 ms |
+--------------------------+-----------------------+-----------------------+---------------------+
| 3. Multi-Table Join | | | |
| (Reviews Aggregation) | Total: 2.6 - 2.7 ms | Total: 2.8 - 2.9 ms | +0.20 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+
Comparing queries performance for embeddings stored in the table along with the source data and in the decoupled dedicated table showed relatively minor difference in performance. In most of the cases response time didn’t exceed 0.2 ms. Of course it might be different depending on table structures, data and individual queries.
I would recommend testing and considering the layout with dedicated embedding tables. It might help you in the future models maintenance and model evaluations when you just add another table with your future model and run your queries using the newly generated embeddings. With a good schema design the overhead potentially can be minimal. In my experience other factors like the model response time introduces much more variations to the response time than a join with an embedding table based on primary keys.
Top comments (0)