Question Regarding Merge Append and Parallel Execution of Index Scans on Partitioned Table

From: Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Question Regarding Merge Append and Parallel Execution of Index Scans on Partitioned Table
Date: 2025-06-04 19:31:32
Message-ID: CACX+KaMRD4kc=-F9PtLo0N0LzjrsJuCyj24shyuq6GsaYKQ6tA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dmitry Koval 2025-06-04 19:44:26 Re: Add SPLIT PARTITION/MERGE PARTITIONS commands
Previous Message Andres Freund 2025-06-04 19:00:03 Re: gcc 15 "array subscript 0" warning at level -O3