On 10/9/2025 00:57, David Rowley wrote:
1. Prefer scanning local partitions to foreign ones.
2. Pick first partitions with less startup costs and 'high probability'
to obtain all necessary tuples from a minimum set of partitions.
Postgres arranges clauses inside a long expression according to
evaluation cost (see order_qual_clauses). So, why not do similar stuff
for subplans?
This seems quite separate from what's being complained about here.
Maybe. I didn't see the reproduction script, which made it hard for me
to understand the origin of the problem clearly.>
... However, it's not all that clear to me how often someone would
have a LIMIT without an ORDER BY, as effectively there's nothing there
to determine which rows your query returns, and there's no flexibility
to change which subpaths are first in Append/MergeAppend paths created
in generate_orderedappend_paths().
Of course, it should be applied to an Append without pathkeys.
However, I still recall user cases where the subtree scan is stopped,
even without any limit, simply because MergeJoin has reached the end of
the inner/outer subtree or in the case of semi- or anti-joins. I wonder
if other cases may exist.>
Also, I wonder if it would make sense to shuffle partitions a little and
let backends scan partitions one-by-one in different orders just to
reduce any sort of contention in case the queries don't fit the
partitioning expression.
I don't follow this part. Are you proposing we randomise subpath list
order? What contention do you aim to fix?I have no specific case for now. From the top of my mind, it may help to
prevent flushing out some partitions from the shared buffers...
--
regards, Andrei Lepikhov
