From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomasz Szypowski <tomasz.szypowski@asseco.pl>
cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: View performance with implicit cast
In-reply-to: 
 <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
References: 
 <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
Comments: In-reply-to Tomasz Szypowski <tomasz.szypowski@asseco.pl>
	message dated "Thu, 02 Jan 2025 20:02:05 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <640396.1736015849.1@sss.pgh.pa.us>
Date: Sat, 04 Jan 2025 13:37:29 -0500
Message-ID: <640397.1736015849@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/640397.1736015849%40sss.pgh.pa.us>
Precedence: bulk

Tomasz Szypowski <tomasz.szypowski@asseco.pl> writes:
> I have got an example, in which PostgreSQL could speed up:

The reason why the first version of the view doesn't behave well
is that it's not optimized into an "append relation", because
is_simple_union_all() doesn't think that's safe:

/*
 * is_simple_union_all
 *	  Check a subquery to see if it's a simple UNION ALL.
 *
 * We require all the setops to be UNION ALL (no mixing) and there can't be
 * any datatype coercions involved, ie, all the leaf queries must emit the
 * same datatypes.
 */

Perhaps this could be improved, but it's a lot easier just to add
the cast yourself.

			regards, tom lane