public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Karsten P <[email protected]>
Cc: [email protected]
Subject: Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union
Date: Fri, 9 May 2025 01:43:27 +1200
Message-ID: <CAApHDvqiUJLw02dM-54BmgvjKjNcOVLS98pM4tkmGtLVRFx8Ww@mail.gmail.com> (raw)
In-Reply-To: <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
References: <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
<DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
On Thu, 8 May 2025 at 22:57, Karsten P <[email protected]> wrote:
> i'm sorry i didn't check that first. it just won't work in my real-life
> example.
> though each part of the query is using an index-scan it is than using a
> 'normal' append
> instead of a merge-append, but i don't know why.
You could try:
SET enable_sort = 0;
... to see what the costs come out to be and how it performs. Perhaps
the planner thinks using the other indexes to more efficiently filter
out the unrelated tuples for the WHERE clause is cheaper than using
the index that provides the tuples sorted by date and filtering the
unwanted tuples with a "Filter".
> so my question is: under wich circumstance does the query-planner use or
> prefer the 'merge append' over 'append'?
It's all based on costs. Those are shown in the "cost=918.40..918.41"
part that you're seeing in the EXPLAIN output.
You could try adding an index that suits all your equality WHERE
clause filters, or some subset of them and put the date column as the
final indexed column and see what happens.
David
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union
In-Reply-To: <CAApHDvqiUJLw02dM-54BmgvjKjNcOVLS98pM4tkmGtLVRFx8Ww@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox