Message-ID:
 <DB7PR08MB30819C2279665D7AFBD247EFDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
Date: Thu, 8 May 2025 12:57:40 +0200
User-Agent: Mozilla Thunderbird
Subject: Re: Feature-Request: Performance-Optimization when using limit in
 combination with order by on querys using union
From: Karsten P <mr.mister123@hotmail.com>
To: pgsql-general@lists.postgresql.org
References: <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
Content-Language: de-DE
In-Reply-To: <DB7PR08MB3081636A27C077E3BB4DA2DCDA8BA@DB7PR08MB3081.eurprd08.prod.outlook.com>
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_|PAXPR08MB7526:EE_
X-MS-Office365-Filtering-Correlation-Id: 4190c998-a8fc-4ed7-153b-08dd8e1f2747
X-MS-Exchange-SLBlob-MailProps:
	HeyTOiyEx6YqUhv2IENF9WKNLXnn5i2fIYj8vz5F5ZJsmuGYqM3PHlnCE6SOSkE1eYnkdBHNG/dpGm5yWZtszZnNTNcbctfSD+v7zLF00mqaiYGTU2uXOPprX/RZJulvI3Px2+/KMqoqKyqXYwG4yElkMBlmz5iKXh+hfNsnKggxzzrFCj5q9/C8qVgDUeCfxD1b6AojjKqjgTPAQAmCAgPlma2Cle6tcjSRxJg4JG0K0T3ZNBfA4PMZPJTdk8CvppPg7HVchlqsLiyE3NjYgAhYnY225ltsNARYW750ZBNoN5Y3A3k9Cs2NBLx1a4LrwRt2r2da+4tvZuY7+/SnSYfyifyZwObjjg4VU1FZ/IZLZH95bPfcp4yqCr+HNKWyLlfxoBrFhKuIKFq2qH/qCom/lZdM9nPsimdmFRNC4ZzQfp+UKuPHkAbcCUxLTGpjcX9a4MPSBYQ9sdVnqqoxvn7O6pwe79d/LHOH78IKFZippLgeAMubhRejh+6kXfSpAAW4RH9AdbjIkJp6AZlIjIjSvFW5yeKpoZBu7WPGq2SzLtvQtJea1olzgjJER0EK4NxlKoZebboyJVUUlRKrHK1WPwdzFjBPGfcroIIN4rdWpi5BYNUkd9E2l5W2kUZ3xIZ9U33UVqa6hWKiBUp8fxmBlUj+75iOyKUIDNPanl5FeIGHPpEvOz3JpZGl3wDQOD+Kl4tu37kvvl5xVz2H/Ys24JpIiZRZOAKDxpN0fUGtHZeLUAiBR5c3gn/+NXTiZjJ1/kMBJeIwl344NOohRA==
X-Microsoft-Antispam:
	BCL:0;ARA:14566002|5072599009|6090799003|461199028|41001999006|7092599006|19110799006|8060799009|15080799009|3412199025|440099028|3430499032;
X-Microsoft-Antispam-Message-Info:
	=?utf-8?B?dHZld2l2UDdMbXB1TVJUb3Z6MFFINjZSOFUrZlNuMWRVK0NvN1lqZUdCcU83?=
 =?utf-8?B?WkdSNUpzYkgxQnJxeEpwZUN2R0tqeVhXTkZaQWZNNGF6QXh1cElWaExBL25i?=
 =?utf-8?B?aDB6Rkt3NWN0bFZEaEswRVFhL3lRdHhaZE5oWU0zOTBkWWZSdWxUUk1KcEhm?=
 =?utf-8?B?ckhXVCszRjVoenRkbVBqZ2hoK0JmT1pCWWJteXNFQ0JNTDFNbzhTWkI3N3R6?=
 =?utf-8?B?N01BMTBGWk5HemUwazRUbFlvY0VXT3NtS1RqbVczQ3ZmS0QwTFl2L3RpYmZO?=
 =?utf-8?B?UTBMb3Vadi9HYnRKcG5RYlFrMVNQZ1RRWmJRbzRkNko3dW5YM3VGOHlBQnhn?=
 =?utf-8?B?b1poUW5GVVZMMnhySG1ZWUE1VUpHWU05aklmRTlrZnZSZE4xNy9DSXFaRjN4?=
 =?utf-8?B?bXkyREo3a0hPbytWazVsWC9RdUxqQ1FqNWNsNFh2NGxGcWw0TktoTDBTdzVV?=
 =?utf-8?B?NE1CUWc5RjltaDhDLzBhanV4cmVrWEVQckVmTTMvODNYQm5ZNVlGbG8xWmtS?=
 =?utf-8?B?bkJtK095WUFhQkdob1NVL2t2RmF5RW5oMVNMNE9TaFBDbktYZ0NvcVdEcEFW?=
 =?utf-8?B?VUhGM2xnVk5pcVgxZDdNdVNBQ2pBK3BwMUlHcEg1S0Q1WVVSZTczeERsL0Y5?=
 =?utf-8?B?K2RPL1FsaktvOXpsMkxkSnBWM01JbUt2WThZQ2g4cGVoWC9MVWdLNVhZRzY5?=
 =?utf-8?B?bVlBSEp0eTQ1YVROWERiWkZic2NsR2wzTEo1dlFuMU5RKzBsbTMvai9WRnU2?=
 =?utf-8?B?WFY2cHY5ODlEL2xuMDd4dWFOdTgzd2pBVGk5MUlMWEJlTVR3Ym9Pd0U4YlV2?=
 =?utf-8?B?d2V6T2U0OUZXcHhHMWJ0VWgvLzBLcFZCQkNPdFRBR3gvSDRHc1YyS1V2WHBi?=
 =?utf-8?B?UXN0TXBPeUpxM2lFRC8rTC9PYVBvRVdGcmxWaEZreXoyY21GbU4ydEE5Z1ZZ?=
 =?utf-8?B?OUFxZ1V5Z1pyMHg1SFZNTTRsdmQzaHA0Qmw2Q2d2UFJhd1VId1VqVitNdWgy?=
 =?utf-8?B?dFZuM0NBQmlHbkR2ZGFqNDBTSHZYdDI2bGRTVVI2YVNMZjRNUk45dnJUWXY4?=
 =?utf-8?B?V1UxZ2V0UzhpaWZyZGJHNmNRWnlRV1F4M1hzUlFuSG1zRnY1SlN1bGs0ajNW?=
 =?utf-8?B?cXJ6UGx4dVRJTUFvSEJjTGttNHNyZzhWaWFHb2lZeG40WnhkeFNpUlVDN1V0?=
 =?utf-8?B?aVZOZkFqN3JUbmtFMUtoQTdubmJuVUlvaTRlMWgwNDd0M1RKWUwvQm9zMXpV?=
 =?utf-8?B?YnBDZk5pd3RVTHhDYmZDMysweWpabkVwbGszVlZ3REZRVFZkZGhGclNjZ21r?=
 =?utf-8?B?SE93aTlzK0NyVDIvYmhCNnlhYXJwOWh3a1d2cWZyclVjTXlkejZ3bUVvNTZT?=
 =?utf-8?B?dnRnYnRVWjhlVE8yUWsvd3BaWTk1eWt0TzRCOW0wTlQ3T2QxL3J2eDk1U1p1?=
 =?utf-8?B?NTR5LzJoc2U4ZjBxNEdHMHRUWWI3N3pRcVBsdHpzNnpEYTRkTDhLWFZ5UDhM?=
 =?utf-8?B?NHpnSUxhL3BpZ2l1cXh4UWEvN09QRUhWUTdKSjJoc2w0dSs3RUlJVTFIVHA2?=
 =?utf-8?Q?1tr+r0Esmh9QhPwtkmalgew4o=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?utf-8?B?WE9nZWFkUm5iUmx4TDlXZ1U1NUpPQ3ZZaHg4VlRDZEtpS01mM3Q3Y21sMWl6?=
 =?utf-8?B?eG9hNFBkdzEycm9EV0xtOTNuNURXNVpxWUxjNXN1a0J2djd1eDcrTTVCLy9U?=
 =?utf-8?B?NHhLTHRxclZSUkVLSzM4ekpESWlCQWxPSVJSQVRxVWhrUW05YmtKNkxha2NF?=
 =?utf-8?B?TndRWDVYSW50QXF2WVBiNUszbGV6ZFRqaTc4ZExkUXZENUFycGJrR2ZlQ0tU?=
 =?utf-8?B?a3djZml3YUlGTjRqb2hsdEdKVTVQOVBCT2RyaDdNZjVNN051RDZ6WHZaZENl?=
 =?utf-8?B?Vkg2NVJLbmpjd2kzN0FGZmZqOFVoQjhjaXRSWmRrT09iQk1jM2ExT0EzMDRN?=
 =?utf-8?B?VUxuS2lqU1l2TGtENWQzVURMaGphUVBzVkRsWVdIbWRlcmZjTVpCK1RkdWM4?=
 =?utf-8?B?cXNWSlNiWHFQdUNjU3VmWTRJNUZQTmc3Qlh1NlUwWXlHTTBsemhZc3BkYUYr?=
 =?utf-8?B?ZXdUUEZMRFlXbXR3MUlGRXA1V2syZytGOGJhRnRmQlR2cVpEd2FjZTM4OGtZ?=
 =?utf-8?B?UDRTN2tuN3NjcTlFTm5OUnBEc1hsZ1M0UmhpSHNMaVFFNXBDQ29CVlNvYUlo?=
 =?utf-8?B?Umc3dW5GQU9jVXowUldTbEM4Lzh3MnJhckxrRkU4endScStTc0tvNjBoQ2xl?=
 =?utf-8?B?SXliSGtDVHlxQTNkQndmL3Rxb013cDY3Ylhza0FOWFFQTHM5YnJnSkNnUmVh?=
 =?utf-8?B?WGF2TWNNVm1XOGRLYWk1RzhRck1zc2Z1M2pCYnFjdEs1N2Z3em9UbTBnZm80?=
 =?utf-8?B?aGM0cXpidC9FTFEvRFBmeENYSXJyWmkrUjd2UWtNdU1ZNmpIWGdjM0lHYVBK?=
 =?utf-8?B?Yjg2ZXZ1TDUvZDA0TzI1eGFVMjV1c1BsMklxRjdiV3M3cHFUU2J5MFZyRDI3?=
 =?utf-8?B?UmlDaFBvR1F3VGVHWWZtZmJ0UjJoV3N0Nkd5WDJPS1hDeXRtNmVVNUhGZWxZ?=
 =?utf-8?B?MXptL2xrZFB4VGdEdTNZYStsV2x1OVZUbytCekNESWl3UVRTVjhFNlNEQjJE?=
 =?utf-8?B?UndQM2lqRVhSdVZYL20wUEtrbzNQZVk4WHBYRVpLTDh3MGVYTDJFbis2UFhL?=
 =?utf-8?B?czZuNTJmOUZzZXZaMUE5VmMwOFIzdytqZWpUZ3JUUkY2OFZXM3k0NVQzaEZP?=
 =?utf-8?B?VlhTaXpVVFRweUhmSmtoNm5KZGNOUWJ2eEJRZHdxTmxBMFd2YXN2N1hOZE05?=
 =?utf-8?B?YmZOdWZpdmJnTUI2clhIaHVRTUZJekZjZk5ndURBTHozcVRubkc0Kzd1cEJx?=
 =?utf-8?B?eGlxeXdZNjkzM1kwVnhudE1hbmE4eHAxczhwNUlmYjBHYjkyRGVDMTFFTEFW?=
 =?utf-8?B?ZUVvZ0UzaEZZTkV6NDlGeUNKN3BsRThEdEw3TG11eGVyaDBBY3ZudXVBZG1D?=
 =?utf-8?B?ZlNNcDhzTlZnaThaM24yRUNUcWZKM1dwNktUQ3VlRzAyTjNPeXh1QjcvTThS?=
 =?utf-8?B?MEFSUG0wZHpZbWd3TXRkNStjVkZuckpSY0tNZVFWbFJXK0xEZUdPR0dGOW1Y?=
 =?utf-8?B?elN4RGlNZkxIbUI1R3dnaDdFNHdGTmNCZXRmQnBqc0NlbCsrYWo0V2F3dUwy?=
 =?utf-8?B?VmdLRjRKY29nWGJkRVNhdzZaQmJQb0lSdUIzNUl6cVZLY3ZCNlBWckRzRlMz?=
 =?utf-8?B?SUZZRmJtcE9GTForNVNyWkQyVElHRHZ4R3BGNG9ZOVIyeEp3OHQvSGI5TGtu?=
 =?utf-8?Q?vIgvjsxz9orc22PkldnA?=
X-OriginatorOrg: sct-15-20-7719-20-msonline-outlook-de33f.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 4190c998-a8fc-4ed7-153b-08dd8e1f2747
X-MS-Exchange-CrossTenant-AuthSource: DB7PR08MB3081.eurprd08.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 08 May 2025 10:57:41.5714
 (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: PAXPR08MB7526
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/DB7PR08MB30819C2279665D7AFBD247EFDA8BA%40DB7PR08MB3081.eurprd08.prod.outlook.com>
Precedence: bulk

Okay, forget what i've just written, sorry for that.

I've digged deeper and checked my generic example - it works perfectly using
a merge-append in combination with limit.

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.

here is the expected query plan as used with my generic example:

Limit  (cost=0.86..0.90 rows=1 width=12)
   ->  Merge Append  (cost=0.86..104314.37 rows=3435900 width=12)
         Sort Key: kpkp_orders.buchungsdatum DESC
         ->  Index Only Scan Backward using kpkp_orders_1 on 
kpkp_orders  (cost=0.43..34977.68 rows=1717950 width=12)
         ->  Index Only Scan Backward using kpkp_invoices_1 on 
kpkp_invoices  (cost=0.43..34977.68 rows=1717950 width=12)

this is fast.

and here is the query plan used on my real-life example:


Limit  (cost=918.40..918.41 rows=1 width=8)
   ->  Sort  (cost=918.40..919.59 rows=473 width=8)
         Sort Key: s.belegdatum DESC
         ->  Subquery Scan on s  (cost=0.56..916.04 rows=473 width=8)
               ->  Append  (cost=0.56..911.31 rows=473 width=327)
                     ->  Subquery Scan on "*SELECT* 1" 
(cost=0.56..624.08 rows=318 width=187)
                           ->  Index Scan using 
soit_erpprozesshistorie_12 on erpprozesshistorie eph (cost=0.56..620.10 
rows=318 width=181)
                                 Index Cond: ((clientid = '0'::numeric) 
AND (activeflag = 't'::bpchar) AND (prozessuntertyp = 2) AND (CASE WHEN 
((typ = 1) OR (typ = 7)) THEN 1 ELSE CASE WHEN (typ = 3) THEN 2 ELSE 
CASE WHEN ((typ = 4) OR (typ = 9)) THEN 3 ELSE 4 END END END = 1) AND 
(CASE WHEN (prozesstyp = 1) THEN true ELSE false END = true) AND 
(artikelvariante_objid = '1064748816'::numeric))
                     ->  Subquery Scan on "*SELECT* 2" 
(cost=0.43..284.87 rows=155 width=214)
                           ->  Index Scan using soit_umsatz_alt_08 on 
umsatz_alt  (cost=0.43..282.93 rows=155 width=186)
                                 Index Cond: ((clientid = '0'::numeric) 
AND (activeflag = 't'::bpchar) AND (verkauf = true) AND 
(artikelvariante_objid = '1064748816'::numeric))


so my question is: under wich circumstance does the query-planner use or 
prefer the 'merge append' over 'append'?


Thanks in advance!


Am 08.05.25 um 11:57 schrieb Karsten P:
> 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
>
>
>