From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karsten P <mr.mister123@hotmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Feature-Request: Performance-Optimization when using limit in combination with order by on querys using union
In-reply-to: <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
References: <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com> <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
Comments: In-reply-to Karsten P <mr.mister123@hotmail.com>
	message dated "Thu, 08 May 2025 12:57:40 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <184132.1746713211.1@sss.pgh.pa.us>
Date: Thu, 08 May 2025 10:06:51 -0400
Message-ID: <184133.1746713211@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/184133.1746713211%40sss.pgh.pa.us>
Precedence: bulk

Karsten P <mr.mister123@hotmail.com> writes:
> 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.

The "Subquery Scan" nodes shown in your real-life example indicate
that you're using views that the planner is unable to flatten
completely, and those are preventing detection that the index you want
to use would be helpful.  The view you showed originally wouldn't be
that, so there is something you're doing that you left out.  It looks
like your actual view contains some WHERE restrictions in the UNION
arms, which I think are enough to cause this problem.  Even then,
though, the "Subquery Scan" nodes get optimized away in simple tests,
which means there's an additional optimization blocker.  I'd look
closely at whether the output column types of the UNION arms match.

			regards, tom lane