MIME-Version: 1.0
References: <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
 <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
In-Reply-To: <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 9 May 2025 01:43:27 +1200
Message-ID: <CAApHDvqiUJLw02dM-54BmgvjKjNcOVLS98pM4tkmGtLVRFx8Ww@mail.gmail.com>
Subject: Re: Feature-Request: Performance-Optimization when using limit in
 combination with order by on querys using union
To: Karsten P <mr.mister123@hotmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvqiUJLw02dM-54BmgvjKjNcOVLS98pM4tkmGtLVRFx8Ww%40mail.gmail.com>
Precedence: bulk

On Thu, 8 May 2025 at 22:57, Karsten P <mr.mister123@hotmail.com> 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