Message-ID:
 <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
Date: Thu, 8 May 2025 11:57:09 +0200
User-Agent: Mozilla Thunderbird
From: Karsten P <mr.mister123@hotmail.com>
Subject: Feature-Request: Performance-Optimization when using limit in
 combination with order by on querys using union
To: pgsql-general@lists.postgresql.org
Content-Language: de-DE
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: DB7PR08MB3081:EE_|DU0PR08MB9131:EE_
X-MS-Office365-Filtering-Correlation-Id: 81db9789-d4ac-4eb4-2ff5-08dd8e16b303
X-MS-Exchange-SLBlob-MailProps:
	AlkLxVwsndlmbqnVVkeM2BobHaL9Maaam1dFctUFD0PCZLY7yhaDAf5gui9d/0KPwAKdQK5ekTtWCkgdKRV3WJTMX1Q1mtGiCJDy5IgB2UYAkxVha55mdDLsLsKCrv074+WsOBj5iv4DovXBaVZp3eLPC8nhff6iABzvvncnTl8kY9iwVHHEI5hqkrETLVwFQZlV8pmA1+Fiw8fhsFFX3D9a/daDV7ySEkFU0tIYf2eZj8jzN0GWviV/1kkPL8GaTQRnfbY0h6z4mN8JJqlsAYBtPWlHHLxjk4jYyXXRE9ou90+09NhLoH3v/+dSdY5PMZe6pls7hxHw6m0g/IUK+o3PkDlLroGbEMpdxcIylUs/G5eCO1RQDv6IqX0GaI//0bFhC6b3FpIwi8e0DOK5N22cm516qld0i5y1BEWzUaHfllseuDj7UbXe7Av5fiTSWqJPjvz0+DiMwXJglsi1gQwUIAYMULO7Yoyl7blenTroslCoqDoR7iRCnwX07sfMs/G0kb9W3G1ebw6XKKbciwJu+fIb6HKcAncaHRQ6CWGtY6jeFtXbwSsXbD6Qb28QdFioyt7YsxTqtpUEreKegVu3rbJuBB8kg9WeHzKCjR4TLGpDVW+aI7CFz54W7Nrw8FMXC2Wen3jPos2aSvqTIw1iQ6wAOfarRRwahXyiPyimJLha77vDPuJA4aCnNEZmy6DlkcBd0Q/fUavGBf3VDMIvZTl/51mHi0zQBQVt6ZCmDWErE+i3DM7C8ZzljRm8nUbZjdM4+sKJ12LjzOQE5FlOh7D1cfKc
X-Microsoft-Antispam:
	BCL:0;ARA:14566002|5072599009|6090799003|15080799009|41001999006|8060799009|7092599006|19110799006|461199028|3412199025|440099028|3430499032;
X-Microsoft-Antispam-Message-Info:
	=?utf-8?B?WVZJK0kybnF4UGRuaUl2MmpLZVVNL2UwR3dpdnM5cmZ3NzY0K09Rdm13bDBu?=
 =?utf-8?B?QkV0QmNuSEwvU0RJMW42Uk5QZi93TkUvUEp1a1hHZ09kU2kyS3lXd3V4QzdO?=
 =?utf-8?B?aEZEMjYxekgrdWd2YXRWWFdyVGRVQUZtdkhTdzBGWXFNcDlMd2psY1VaWC9E?=
 =?utf-8?B?SkdVV1VKbERyN1ZydmxJcW1wZ0xwODFUWnJiV2JUOFhmS24rbHkrbjc0dCsy?=
 =?utf-8?B?SGRUZGdQQUNIUW5aKyt2SnE0UlVSZXhsTm5KTHVXdzJXTmFHYVplTVJNLzFS?=
 =?utf-8?B?a1Ixc0xPazJ0clZWWitEK0JTejhmWG8rWndGT3E0MkM5dDBIU2pqU214M3FB?=
 =?utf-8?B?cGwvbG12OXJZUlAxaCsvYmNxZWpmS3pqVk9YdlZNWGkrU0N5NWFNZThGQklC?=
 =?utf-8?B?THVCdmZURHZpei9DMUV4dGswNkFPMEdUdjR0Nitxc0VDV3dUSzBhSThQQjBr?=
 =?utf-8?B?VFV4bTVoaDEwcTlrRWlhMHAxMkpBcmY1dHBsQ2lBeGgzWDA1aE1uMFhWN2c0?=
 =?utf-8?B?OFVUZVBoTVU4UUs5SlVNbHB5QWFWQlJCZWNNUm9YOEI2alF3b3dhdWlua0sr?=
 =?utf-8?B?Y2piWEZpNmtsT2MrYUpQODF5TFpiZGNiOE94KzBzYjRSL2gzc0tTbTJkZXBF?=
 =?utf-8?B?VFFIcmxoam10cmhZdG9GMnd2K2k2OEhHRlpnSDdvMXg4SjJiWExQNHNzbVhY?=
 =?utf-8?B?WmF2RWR1UUs1cWFNMTVYOFBxRTNNR2QrcG9vcUJOTzFhZGJpWlBvelNUeTEw?=
 =?utf-8?B?YU43Y1VRS0h4YXVzQjRLdWJMRGJ6WERFR0FFWUYxT2ZFL3JCYzBZaEk0MFNR?=
 =?utf-8?B?dmFValBxMGUyK3lZcGt1TFNUYWpKOGg4UDVhY2VGcWF2bG1GS2pxUkUxL0Fu?=
 =?utf-8?B?bmMxcldZc0tNaWd0QTVwYWp4ZjFpNVFjSjR5cXF2VXZwVjhVU0lLN0dXRzA1?=
 =?utf-8?B?OHdOekEydU9OVXVqa2lmOThxTHZ4eEtFMm00a1N2TzFhbE1xS0FXUzFiR2RI?=
 =?utf-8?B?WDYxY2x5RGVOYk8va0dXRU5CQk9tWDV4Zis2MnlqRHFkdjZqQ2dIVHJ3ZzlU?=
 =?utf-8?B?MzUwd08xemtkVldjNVFrZTRWV2JXMjRKTnNSR3NobnlZYXJwaEx1Zm9mZ1JQ?=
 =?utf-8?B?KzBpRXJwY3ZFYnRNSXdKOHdTOExPUkw4dEN2ZjlwTEZPWjhLdzVESk5TeThr?=
 =?utf-8?B?SkQ5b1o3Q0V1S2c2Zm40aC9KNUdabVdVYXUvcVdlWVV3Qml2YytNNTZZVzNz?=
 =?utf-8?B?a3N0VW5QNmFqaDRNdlA0RVo2ZzFlTVRQZ1czYVI5bUtVdmMwbjh1Tk1vMTli?=
 =?utf-8?B?eG5pK1pZN1ZVS25pTk81cEJxU05KcHZRWVB2dEN0YTRxRlZkbFFqYTRGaFZk?=
 =?utf-8?B?T0JqUlBiMVlIZCsxa3V0K2ozbDcyWlp6QXJqc2luNmlHTWFlcklMakhNNkYv?=
 =?utf-8?B?Mkg3NXlaQ0E5dWNYNEpQOWs3NmRKallMeklsRHhxYitUTEZ4OFR1SDNLaHYx?=
 =?utf-8?B?SGd3Qms1WHVVK05YMkpBNThVQ05DL2VHNWdmUG80MTJmNkh2Wm1sY0RjWk9k?=
 =?utf-8?Q?8dFgVyohiy30JQ9cVBKq2xlCY=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?L2RGNHRZYXBKc2xCRjhNY2JvQ1U4UW1odHJiamp4Yy9wVFZYODAzUmN6bVNF?=
 =?utf-8?B?YzZzcTdvSjh5em5McDY3TnV1VHE4V1VVL2RNRG9tSU80RW5MQXFVRE9SUldN?=
 =?utf-8?B?Wkg0cXdOdnJJSFNIaWNlaEROZjkwSWQ0ZktLOE5BNGJFenpteXdwdERGcVF0?=
 =?utf-8?B?TkUrQThvYkEyNlQ3SS9zNy9UU01YWmVsaVZPSHlxVnV0VzBobzJFcmQ2M3dB?=
 =?utf-8?B?U3kyRHl3REVwSWh1M0QvMlVycEcwdXBwQmpIcGVXcndkTEgxUmdsSXJOd1Jj?=
 =?utf-8?B?a0lvYnY0aE5ERlJUQmtld3l1ZTdxNngrY2dQMGdFQkpsME01elBzdUNub0hH?=
 =?utf-8?B?YXhyYjJkajV2aDhEUFIwdDV3YXNSTEJhbE00ai8zUWdOVlhPKzBLVWFhT3o4?=
 =?utf-8?B?cFFFd2RaZ2VNUzVsWVpRQUU0YmVpR3pPMGR0WlVlb2Z6citNMUNPWjVNaUN2?=
 =?utf-8?B?MDRSVUxDNzV6Qldtd1BQYXBqNFRQcWcrakJIOTRnVWljOEFFYW5PeVpyK3ZK?=
 =?utf-8?B?Q2dtWGJoQnVpT28zQVc0N3VSNnp2RUFxWmo0ZUw0TklXVDN2NFM0d2tMZXps?=
 =?utf-8?B?RzNsa0haUUFwM25venI2YkRFZVh4czZLL3dJeUpIcmxkQ2RqcTZmNlhVSGR4?=
 =?utf-8?B?R1FyZmRiYVkyM3M4MEdkVG5IQlI2SFU3MnFDVDNrR2lKUnBXNEs4a3FFMWR5?=
 =?utf-8?B?R0ZLUkxoa2JhTnloWFFZZDdybmlJbGZlMzA4Q21UdUI2Zk53WTRLaEJXNlhn?=
 =?utf-8?B?dkVrRGxRNmgwVzdncUhSWEROS09kTkF0S3VYVmt4NVJ6ZjJocnkrcFYvSzZS?=
 =?utf-8?B?MnVpRXF6cWdBc3FORVM4NkFrQTJ5U3l0NmM2WCtDTFdnNUV0NEJPZWJmRjNE?=
 =?utf-8?B?YytoazBFVHBqMjYrUUNlSXVxdm1wRHJiRk8wNCtudW5ETVhsQlJSMU5yUVlV?=
 =?utf-8?B?ZjJCZzVyNE1OdTV6anJqa05oekdleEJGOHpnd3Nzc0JMNkFZMnNoai9NNStN?=
 =?utf-8?B?R2RSMG94blpIZjg0T2cwZS9QSWd6OGRKak9uTzRzVUZIZXVoYVhGd2ZWajA4?=
 =?utf-8?B?MWJ6OHc5MnJraVRuZkJQcDNSVWxTcEFOUzNTWmRiMkMwdEdScU43MzBSYlBQ?=
 =?utf-8?B?eGxpOXJSZ0xKQ2RaTFAwWDg0Zy9MbjlqeWRKcWR0YWlZUnpiUEtEYXU1S3hM?=
 =?utf-8?B?dVBqWkx1Mng0RFdZNW9xK1VxTE44N0Zkb2EvZDdLTHEyT0JkSGtwZUNRaGZ6?=
 =?utf-8?B?Rks4M3RsNXpXd1dTZ1VlVVFEVkNoMkpjR0tFZ0swaEM4N1dtZzFMb0taeXcx?=
 =?utf-8?B?b1JuaE5ObFdFUlV3akhrNU9YcFIvOVVzb3QxM1dQcUZ6NDRUM3lFeW5nYmJJ?=
 =?utf-8?B?Y2Q2Uk0ydUphL1l6L2RPQktTaml4aE1may95WGppakt3Um96SkFwcy9JdEhB?=
 =?utf-8?B?cC8zQURMVUQrYUoyTWRCaWVKN2lJT0VWMmFXbUVDeE85elVzY3B6bTErdCtU?=
 =?utf-8?B?ZkxqNjZzelNqeVl5THVsK3RVdnUwYmRoV0VYcUtQTmNtYjMzemgrWi96YWd6?=
 =?utf-8?B?a1BJazUwejBIendsRWM1NXJ0eGsvYVlDV3F4dkFObTJDUm4zdW9RclBxemVi?=
 =?utf-8?B?eWJLV09Rc0poSmpxYUx6WVRkbEZ4VnZ0THZDN3BuTlhRM2RvZXkzSE4rYXpp?=
 =?utf-8?Q?MLjDtys0bMN+fVghIE2s?=
X-OriginatorOrg: sct-15-20-7719-20-msonline-outlook-de33f.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 81db9789-d4ac-4eb4-2ff5-08dd8e16b303
X-MS-Exchange-CrossTenant-AuthSource: DB7PR08MB3081.eurprd08.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 08 May 2025 09:57:10.5584
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg:
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DU0PR08MB9131
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA%40DB7PR08MB3081.eurprd08.prod.outlook.com>
Precedence: bulk

Hi,

i've already googled so far but didn't find anything regarding my problem..
I hope i'm here at the right place.

Following situation (this is just an simplyfied example):

suppose we have two tables, lets say

orders
   - column 'order_number' -> varchar
   - column 'order_date' -> timestamp

with index on order_date

and

invoices
   - column 'invoice_number' -> varchar
   - column 'invoice_date' -> timestamp

with index on invoice_date

and many records in both if them.

now we have a view combining both of them as

create view documents as
(
     select order_number as document_number, order_date as document_date 
from orders
     union all select invoice_number, invoice_date from invoices
)


finding the last order placed in the database ist really easy:

   select order_number from orders order by order_date desc limit 1

will result in an index scan backward on orders

same with invoices only...

but when querying the view

   select document_number from documents order by document_date desc limit 1

seems to break down to
   - collect all rows from orders
   - combine it with all rows from invoices
   - sort all rows (descending)
   - limit to one row

with many data this is quite slow.

I've tested this with PG9.6 and PG14, it doesn't seem to make a 
difference (correct me if i'm wrong).


So my question is: What about optimizing the query-planner that if

- a query with unions of selects is executed
- and an 'order by' in combination with 'limit' is applied on the 
complete query (not only on subselects)
- and there is a matching index for each select

the order by and limit - part of the sql is also beeing applied on each 
sub-select ?

actually
     select document_number from documents order by document_date desc 
limit 1

is beeing processed as
     select order_number from orders
     union all select invoice_number from invoices
     order by document_number desc
     limit 1

but would it be possible to let the query-optimizer expand the query to
     select order_number from (
         (select order_number, order_date from orders order by 
order_date desc limit 1)
         union all (select invoice_number, invoice_date from invoices 
order by invoice_date desc limit 1)
     ) as subselect
    order by order_date desc
    limit 1

as this would use two (or number of unions) index-backward-scans
and than only has to reorder at maximum two rows before limiting to the 
first of it?

this should be significantly faster.

thanks a lot and greetz,
Karsten