From: Tomasz Szypowski <tomasz.szypowski@asseco.pl>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: RE: View performance with implicit cast
Thread-Topic: View performance with implicit cast
Thread-Index: AdtdT8D9FHqkp7G6RrOZJX+LMa4J3ABh/RKAADq7FsA=
Date: Sun, 5 Jan 2025 22:45:14 +0000
Message-ID: 
 <DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com>
References: 
 <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
 <640397.1736015849@sss.pgh.pa.us>
In-Reply-To: <640397.1736015849@sss.pgh.pa.us>
Accept-Language: pl-PL, en-US
Content-Language: pl-PL
msip_labels: MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Enabled=true;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_SetDate=2025-01-05T22:45:13Z;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Method=Standard;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_Name=General;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_SiteId=88152bde-cfa3-4a5c-b981-a785c624bb42;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_ActionId=ada6df5a-d03e-45ba-9284-f130fe58fe03;
 MSIP_Label_e7dff87e-ca3a-45ca-8165-560d8adcfaef_ContentBits=0
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=asseco.pl;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: DU0PR04MB9419:EE_|VI1PR04MB6799:EE_
x-ms-office365-filtering-correlation-id: d38d2382-ddd5-4385-1dca-08dd2dda9eaf
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: 
 BCL:0;ARA:13230040|376014|366016|1800799024|7053199007|38070700018;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?jHdwm+hQFqfH9rRbaFOXOLdKGZR+WZbA18y1MHgRPSbFGifrxbQELX01CY7D?=
 =?us-ascii?Q?UvgALCmu7eOKA0lMOEmdOpj7JmL3jYgIahr0wrXwCyBPabYyV2omNmRbeVcr?=
 =?us-ascii?Q?JuavHOPOKQ3JniS19S1iqedwLC77LCNXAdi5LvwIqYy6R32XsQMj8kgh6v5J?=
 =?us-ascii?Q?BW0Ohpb1VXXzDjh/661vfsKCCMZlpNjKc8GzJ+CiE4yJnIBbUHZkGAHE5NHH?=
 =?us-ascii?Q?cx3jlHFyBHTFMC+erlhRLgmdWXqFgUEkDSdgYyQCiRQi3RPo4KrefemjMNu8?=
 =?us-ascii?Q?Kg9V1sZTuesU0va481sJQoC93aVivHB9sWbKRpjRoHH6kpPhQ6hCokA6hDzO?=
 =?us-ascii?Q?CIadg+gR7ABJG+J01juavvNWC4+/e51UE/hoQpZLxzekmp44NHs1adJpl7Pi?=
 =?us-ascii?Q?M+HT+XNNZpY6yGqISrOXrQ9NMZsPOngeJaS7OkowAdZtver9BTWOdnw0cdfW?=
 =?us-ascii?Q?4KvyD69/hknvKrdeEDXyL2A0/bGbHS+YjS6IDi/WFoFAZEbjEMPS3dk0V27k?=
 =?us-ascii?Q?KWMOz9emKNnTL5rpKm5zphsYiTbnrDBY7Ipzb2WLykhCcesfiJ0ttC9DZKyT?=
 =?us-ascii?Q?XDNr2MYWyXnOMhmK2p4kPsa9jcxIhU7oOZvTvY9bsPKu06cOEc1EKTTeH+Si?=
 =?us-ascii?Q?trUWT6tVNwypDdhBzyr6mXxJDTg/XUas57enqiIxQPZPA3V4LkeT1un751gP?=
 =?us-ascii?Q?W3oc7FxUkTmaFh/9zVaPq1vLmHU0CkJE++daIs/ozPFXpkfCN9cctWsJh9/h?=
 =?us-ascii?Q?Z7GrBz2OgVPX4uHTsw6PeLJwUqX++JOYX1uLKaIzdsFQsZKpYXaaZLwx9D4V?=
 =?us-ascii?Q?jjgDUhPac1/mx1dJpkXQ1EiSc8KI4vpRpHtm4GT82fYPkhwIq2AZCpBb3OP8?=
 =?us-ascii?Q?+qO43uwR+QIUSYTwPhGJrb2ZweRVymzsIiFM0jNb4aDDr/B9TUKWW4Le3tL4?=
 =?us-ascii?Q?vATHcT+LHCjL988Ps3popkto6eFweUbPknAKrAnmblcYpiQvN9m9eGTm24xk?=
 =?us-ascii?Q?kfGeNMJ15pHClzX394isjm3e+SdUAP9XPojiyY1Q+EPzTVwxYrZdfExYRVsM?=
 =?us-ascii?Q?BRgihdGT3SZcDIF1mYlvvk+mKtCAcZ91Plj8rkSTxG17vQzcRF3Vn+04+x25?=
 =?us-ascii?Q?93vK2ZuhE3iVZ/+B6WzQrVrz2r9v6b+dwWac3fLzpZqZX0z85kgm5Gy+Md+N?=
 =?us-ascii?Q?ruR/NE5ENAhSOcbFdz5RSB5PDp2lXH40vzRFBoqeNip6lCSYTaeU0YIAEfdA?=
 =?us-ascii?Q?zENewVtxkL3ARPuOYyp9C62l94JZGgQF+WXX9RvJ3l2KWEO0H8ixNrx3aUOT?=
 =?us-ascii?Q?gYxQbCw49VEzJb2GI9SGQ5W7+0zu0fW2bWBfLWHsnOe4M+bGKZpSJnmb3NVm?=
 =?us-ascii?Q?Bn3TNSu8iTnWIbpVMiTtmAQpI/zZ64Juj8qlQ5DQaXgqR52eJj2Mj8hBrVBB?=
 =?us-ascii?Q?MOzjqiEu9dNfPTzSXK7hfBZzghmOfzQh?=
x-forefront-antispam-report: 
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DU0PR04MB9419.eurprd04.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(366016)(1800799024)(7053199007)(38070700018);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?C6+ozh17TCmEgduwKGDcgaSwFNJaSs551buE4lJaAiuv8BRhn+je0RKDs/7W?=
 =?us-ascii?Q?nm6eHcK/kDC4ee6Kow8oGr32rY7EpQ49eAR3i6wi6+3Vc1eKy89JHXmeaywF?=
 =?us-ascii?Q?/3Z4nTergtmXaJo+YIFxOrNVPDH23ASl+bZ7w8a5rWMnJEL6jwm/IdJtx1Go?=
 =?us-ascii?Q?QJGsmlaxJkv5XskLX4Tmdxp8OnIwRCSG6uGJhXb+f61aiodeqgly+TFl17QO?=
 =?us-ascii?Q?V+4+kOpYQ1IIWrgXy7nbqRyJUI/sEWcla3gyvZYPm6pIqwYGIdkA4if0sGtm?=
 =?us-ascii?Q?dT4IoJ12AwHx3OScsFhwPnk6BgvMRPpcBhCQugGGeFBKn97loTmbRJL/Y3JN?=
 =?us-ascii?Q?XtIWPzsT8Ka7KLQNltYnx3aHednq0iZb99siTzhTd3ZFIEyiBpT6m6GlnwcI?=
 =?us-ascii?Q?fwfF/i5Gd0P7NjFqJzRl3hlEna2VhLCmtjoiaFnWORwXlQVpvCAqknna5+aP?=
 =?us-ascii?Q?bac3dLO9SFpGuloeTqYnhU/019iZNM7xkISmdKMzGuxo8OJcZAjVjuXnnXts?=
 =?us-ascii?Q?raKMLxQvysrwvMNMcsQA8pTfZJM6Yxpx9yfHfvmjNRye5nPtwQ6E0UwBox4O?=
 =?us-ascii?Q?z3QACprx/nndyQFlHKCCGYp9cBfSbvmmcNqAPkkpreY8NM9ynD5v+RVTbca5?=
 =?us-ascii?Q?GLSwSWo0QQ4CRo9+VSZnPPunWgM/XrUbvmwnt6ygWrc0eQ07dC5YdfoLTVWx?=
 =?us-ascii?Q?96xAVkxgJlfdnE6SD6KZ0MBSgRvstXajonXL4GLD6CXeQsNN6cWNzDtZuiK+?=
 =?us-ascii?Q?Cwl2gwOrXWU/jsH12bfWmLbEttwTIs8K/vD+fDGo1rr2CKW++1HxrPyAA06Q?=
 =?us-ascii?Q?kN4ckDWCDuwg3a8QPV0U9V86nNkzVxaLeb3+hFhzRsPHHqMznm+09c5yGpZp?=
 =?us-ascii?Q?ZlSo4lvivjqEpve3NiqycT7aMRFnVkUBQd0L6ntCN8TQn9ohgs42K+uJo0IK?=
 =?us-ascii?Q?4MKQrHap/zb9b7aHEswHa0ulenSZI3aHOqKVrKUN4bDLmznNY90XVVxfYdpa?=
 =?us-ascii?Q?dSzrn9Z3ESbDa4HYsTiID+Xv0Dnp0SGymYKSyWJlUVBKiKifY3VXlpAX9fN6?=
 =?us-ascii?Q?2JBzQsRokKTcvo6gLSUyWHEHiZmBg6rlwY4uLKArDvfT2w1/oDfOLCQA+46m?=
 =?us-ascii?Q?nmLzS6RMqtj68SKXPrbNNUQBUrneeRQCkHGnPFWBCjeAk+CHBza0lK1cYxqc?=
 =?us-ascii?Q?vl2fX+YzxtZGwRD37JTaFuObscFSIqLcXuy+KtvYu/URbN6bkxsJkWrifkaG?=
 =?us-ascii?Q?A9uqFaKhsmq05STD9Ix22v6m7/RRgRDfSBGPfTmx6sqM4biauDKtXT19da7O?=
 =?us-ascii?Q?zWMPN322Cyi5clNZKgODdubnSdBmHezxDv7TdjA95SZn4KhhV20o1UqQfkiu?=
 =?us-ascii?Q?6tFc9h+Qdt6DZft2oB4O68zSe8tSsY/cyDNmsqJYgbiTKVH8CYZlboThTn8n?=
 =?us-ascii?Q?I1QTI4JjNpT5bILXdYtu7TYFrzFrGOslvdSW5XNTqFDnfFDfDku+y7/RD4Lm?=
 =?us-ascii?Q?ZmqBYc0JV+8vx2mWdA3AGcnl5CrExZV4zCwphNX9YqCL5QL2VwmruoCXdYQo?=
 =?us-ascii?Q?k2nv4YUcaVEY8ZEUMRr4UMd3sMXZWqmwQl0vPmQO?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: asseco.pl
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: DU0PR04MB9419.eurprd04.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 d38d2382-ddd5-4385-1dca-08dd2dda9eaf
X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Jan 2025 22:45:14.4616
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 88152bde-cfa3-4a5c-b981-a785c624bb42
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 avm/zc42z7b6KDWgFzZl+27ZMU0fn39WLvQU1HSprPSqicCVA0MJbj/Cy4RRzpNhm27l+Sanmj+3bD1w6Zh1lKdp0rxQtUQHlXAxIdT03Q4=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: VI1PR04MB6799
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/DU0PR04MB941961BC101B5B4AFB0C1C1699172%40DU0PR04MB9419.eurprd04.prod.outlook.com>
Precedence: bulk

Thanks

So how can i create the view, with different date data types (date, timesta=
mp), so that both indexes would be involved. I see only the solution with i=
ndex with casted value (create index ... on ...(cast(... as date))), but ma=
ybe it is a better solution?

What`s more i found this strange behaviour:

CREATE VIEW test AS SELECT * FROM foo

CREATE INDEX test ON foo(id)

Now if i execute: DROP INDEX test, I receive:
ERROR:  "test" is not an index
HINT:  Use DROP VIEW to remove a view.

After executing DROP VIEW test, I can drop this index

Regards
Thomas Szypowski


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Saturday, January 4, 2025 7:37 PM
To: Tomasz Szypowski <tomasz.szypowski@asseco.pl>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: View performance with implicit cast

[Nie otrzymujesz cz?sto wiadomo?ci e-mail z tgl@sss.pgh.pa.us. Dowiedz si?,=
 dlaczego jest to wa?ne, na stronie https://aka.ms/LearnAboutSenderIdentifi=
cation ]

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