Hello Hackers,
I had a question regarding the execution of the following query plan on a
partitioned table with vector similarity search:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=398.95..420.97 rows=100 width=12) (actual time=1.750..1.912
rows=100 loops=1)
Output: vector_items.id, ((vector_items.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5)))
Buffers: shared hit=2130
-> Merge Append (cost=398.95..22420.04 rows=100000 width=12) (actual
time=1.749..1.899 rows=100 loops=1)
Sort Key: ((vector_items.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5)))
Buffers: shared hit=2130
-> Index Scan using vector_items_p0_embedding_idx on
public.vector_items_p0 vector_items_1 (cost=99.65..5250.52 rows=25126
width=12) (actual time=0.461..0.495 rows=26 loops=1)
Output: vector_items_1.id, (vector_items_1.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Order By: (vector_items_1.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Buffers: shared hit=525
-> Index Scan using vector_items_p1_embedding_idx on
public.vector_items_p1 vector_items_2 (cost=99.68..5223.56 rows=24978
width=12) (actual time=0.420..0.460 rows=29 loops=1)
Output: vector_items_2.id, (vector_items_2.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Order By: (vector_items_2.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Buffers: shared hit=494
-> Index Scan using vector_items_p2_embedding_idx on
public.vector_items_p2 vector_items_3 (cost=99.80..5227.42 rows=24971
width=12) (actual time=0.422..0.454 rows=27 loops=1)
Output: vector_items_3.id, (vector_items_3.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Order By: (vector_items_3.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Buffers: shared hit=550
-> Index Scan using vector_items_p3_embedding_idx on
public.vector_items_p3 vector_items_4 (cost=99.77..5218.50 rows=24925
width=12) (actual time=0.442..0.470 rows=21 loops=1)
Output: vector_items_4.id, (vector_items_4.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Order By: (vector_items_4.embedding <->
'[0.5,0.5,0.5,0.5,0.5]'::vector(5))
Buffers: shared hit=561
Planning:
Buffers: shared hit=38
Planning Time: 0.384 ms
Execution Time: 1.975 ms
My main question is:
-
Are these Index Scans executed *sequentially* (one after the other as
the Merge Append requests tuples)?
-
Or are they possibly executed in *parallel*, in advance, or concurrently
in some way?
I have configured PostgreSQL to allow more parallel workers than the number
of partitions, but I'm still seeing the same plan without any parallel
execution across the partitioned index scans.
I understand that Merge Append requires the first tuple from all child
nodes before producing its own first tuple, which can make parallelism
difficult. But given that:
-
Each child index scan already returns sorted output
-
There are *more workers available than the number of partitions*
... wouldn't it theoretically be possible to execute these index scans in
parallel (one per worker), with the Merge Append node just merging
pre-sorted streams?
Would appreciate any insight into how execution and planning behave in such
a case, and whether there are ways to influence or improve this behavior.
Thanks
Ayush Vatsa
