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.