From: Tomasz Szypowski <tomasz.szypowski@asseco.pl>
To: Tom Lane <tgl@sss.pgh.pa.us>, "Zornoza Sanchez, Jose Blas"
	<jbzornoza@sia.es>
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/RKAADq7FsAARSMCwAAPI9MAAAr5/mA=
Date: Tue, 7 Jan 2025 20:07:16 +0000
Message-ID: 
 <DU0PR04MB941950CC9F69A0F3B0FE877B99112@DU0PR04MB9419.eurprd04.prod.outlook.com>
References: 
 <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
 <640397.1736015849@sss.pgh.pa.us>
 <DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com>
 <PR3P193MB073140BB6374E47E7E6EB87CCC112@PR3P193MB0731.EURP193.PROD.OUTLOOK.COM>
 <1468967.1736261534@sss.pgh.pa.us>
In-Reply-To: <1468967.1736261534@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-07T20:07:16Z;
 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=5f1d61de-4d14-48ff-bdcc-46aadcc75d2b;
 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_|AM9PR04MB8413:EE_
x-ms-office365-filtering-correlation-id: 9782af27-6bc6-4e20-0d76-08dd2f56e274
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;ARA:13230040|366016|1800799024|376014|38070700018;
x-microsoft-antispam-message-info: 
 =?us-ascii?Q?zbJf/3xkUnjtN1ts9X7tQDV46OEyIepGEdvoF2MhoXkQrwEONdn1SdrArCJh?=
 =?us-ascii?Q?MywR6zTl5RlMmrIlJ82/GTOTz3WrEFwMz0AELaXKb2iXQTo9+FCzglikFn1p?=
 =?us-ascii?Q?faxg1TwsJkCiCsqHihS/PETfC+gS98YDRhSppDPnIW4+tm1UMWTbmD3clcp/?=
 =?us-ascii?Q?LLS5EOotrwj25NAXLP8wRfbYBs3cznH6Rcs/RoovqbEwynxGuCzWK3DigXMc?=
 =?us-ascii?Q?bK3rjAheBjJOysSLkyKD1ZiY+AD2iWuYDj3yM9Aj1VhPoCIILPpPuVrxDcMA?=
 =?us-ascii?Q?7tP7gZhBHrocphzWKXanpqyWYtHyXYoWghu9y1Ya08B/UWzXDxSOuB0rStep?=
 =?us-ascii?Q?EkCYWKzMiAxSLrud1A4wft0u7dJ7WzbPtZkuxm+Gl466uHuYX5iXsNlfackp?=
 =?us-ascii?Q?pJDb1dWdFHTr2whGOSZ953zsiC0uvG4mWesRHrph/BQq0+gM2vtATLzJhAxy?=
 =?us-ascii?Q?z6atE547o8moM7lKZxwC8RbGzLofxYRx1miD75GhstRUDxB3vKA7KzA4j9Kp?=
 =?us-ascii?Q?idH5g72UlONhDzbefSojjcJ/X4Ylndlqkx/2npTyyg3q7a9M7Dto9c0PTE3v?=
 =?us-ascii?Q?eDXfABuvMXSbhx1xF5ii/syHo0Xr+vBXBdYZVrpf8Pf4SxfsecVnlwvmmBf8?=
 =?us-ascii?Q?bNexeC1BR4ryAHtusuvarOoJK/ZZtJZJlW5E4noj3aJlJL9l/52Z3ZrrFumO?=
 =?us-ascii?Q?3hqP+9rjOPTxp2G4WwUfhVWIQ4ScKJ6v6VNjTl3TQIj11XQGaOqgSPwYlQYm?=
 =?us-ascii?Q?FvGHmkroa0FM9/QtMZYE56g11iU34DDvIo3fppCqdI5Z635TV/OrmfCvtdzm?=
 =?us-ascii?Q?ArNk0bQ4MM7d6TJLZl5fHn9kySCvE5D3BIA4EWE0/PiLMB1vshnZCuE7Ecz8?=
 =?us-ascii?Q?7E6g6W2xVQkGqGVwhL5WHji9/ZU6a2tsv5v9ciM9ThWbGL0O4WHC+Bi/0FRS?=
 =?us-ascii?Q?MluiJd5Ibj5WWUgHxM8CS/iF6fe9drvjGKlD4NZt68Xnb4RKw/Qw+IK4zL2a?=
 =?us-ascii?Q?BhrYGEewhqmbVslY6RzTZ0D7XUX2/iBZi+S6VCn5AVMfWoDigy+4qoAMnfl+?=
 =?us-ascii?Q?AZKndapG32spnGtrDxKzAEyHs2u6mhEBbBrwy386STi6W7iNeFcrGvlFVya8?=
 =?us-ascii?Q?gnc2Kg5fTBsTzDfzP+EbQOUrkII/VIagiQNe1wPcw9rm7wou2HyuZi+n/XrU?=
 =?us-ascii?Q?BbVmNFHZ5drrnNiTUMDq+7FqqOvSrwZALbUWU8pZvmCMABz+awzBB+Zi+f93?=
 =?us-ascii?Q?rRdsA1ez6r2dA3PPGH8KVWHMymAEuhvBObjnvZUaOjsEPHe0fPjZIgH7m1Lv?=
 =?us-ascii?Q?eCSfEvLFPpME2euLIdynC5LM0m7HRucPuQXMaeCxDrK5zI5ScqYTQmXL4Vyj?=
 =?us-ascii?Q?IuxMFsKqPeqgYW2WWnXwQrNYErc6RdJpTHu+Bxuq3G9wRawmwn60e0sHGHCF?=
 =?us-ascii?Q?lKXwsGlojtAIaUkJuLbkvBrLPvYraMe/?=
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)(366016)(1800799024)(376014)(38070700018);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?l8MPUdbrgJ/va+jNOAcdX61GujUx/AWgUvZ4ho7N4lp7uceqb7GSC29lLriK?=
 =?us-ascii?Q?kMH7K5YCHtGgNJlTN1P3K01MoITjDjMjiJLViHywiCrWRyTwgBhqYkfzupEy?=
 =?us-ascii?Q?w/qe1p1BMJCUbB+eu8xLtlGaK7bzl0q5dR/9iDlpFa8IveD8SZw21leptWP1?=
 =?us-ascii?Q?hDrZPu5dZY55KxYa51g88VVjUSJwmBSliWySyILPA9R5C9t8Dwd7KFZCPpzY?=
 =?us-ascii?Q?9uJEH5FToCokqFsb9W6TCqH4mIhfL7cY3GI7rJkuybJ2tm+w/BrVhHdHnwwS?=
 =?us-ascii?Q?fsmcNyKxedp6TRCEzCpX12Dpyg8Em1sFatg+8MiYMItXGElJaudVBhLkFUyo?=
 =?us-ascii?Q?VHvGX3Vx1JLRmMe5Mb1LJBM3KG0cEdFsmLXQT9dd1TLC5u8O1D6aImJCVZMr?=
 =?us-ascii?Q?086Va/sOq8GKcNjyrYAAzvMUGcAevvFLiwu+RYPBCJTnv+grdv741d+zIEh9?=
 =?us-ascii?Q?hRWKwvb6uw6prkxla4tFORksPflxrUlPtXoOT5meJa76hGNfIIj5emQSp4Ki?=
 =?us-ascii?Q?+z65CEurgIcOyrspI8ArXZOIgFnJG3JToKUgdcCuxM2qEoMJPZjeTRWJSDjE?=
 =?us-ascii?Q?ZB/z/trcHzz2VkyZ3eOJykguBhzzMiGiVDnE2Avb9A0QFgsL+gxZHnCKeJA9?=
 =?us-ascii?Q?Rnj+p/6Et+ZV5rqMSVuwcnxpoUKnUbnMwnV2I7NQA5Lnn9QeiDHsNgOMrga8?=
 =?us-ascii?Q?hYzw2eXUr7mhoKQbj9fptkJ5mcJY/mlX9V9MFVs16Ia6L9QlZkzTL8CtYFA/?=
 =?us-ascii?Q?tO4eguR3eohI9OmA9etLIq2bhO/e32IYj4MSCnTsaztAGwv0wZiYNIzhBcqB?=
 =?us-ascii?Q?krHFsc0DR/A8j+3ZOgjiCo2q3W1VrCRU54VygfecqBsVRBrhdZZWe937PGBN?=
 =?us-ascii?Q?aZXS7U4IAdDl8KSZtd1/KAUOPiVcWOjCNers1dlEXoKKon9kQIxWkPwQ41hb?=
 =?us-ascii?Q?ekMCLZRS3rOXYpNW1K6360KHcDlQP1Hw7UI0CGf+yv2WWDNtKCkJqK1V6DfX?=
 =?us-ascii?Q?4pordp4w30e4xYwpbKczSutbkewjkA+3ciJB1C/cw8M6p3kKqJa2XKWzK34t?=
 =?us-ascii?Q?FCs6t0/ttqAasAdE1DTZNdN3T+ZN3QH9sW2w3WcvyFC6hmJwcnPptIuzl9Ch?=
 =?us-ascii?Q?7PP9HKRu3SpaDLCZOotPhiaqvZGV9CClzlxrPdXJ+aBH145cZIlTWjhMVxRf?=
 =?us-ascii?Q?eYbILW3iTExYdvy1wzocQHO/FmFOzKm35mcky+uckYriq9wb5DhU4uhoqYaK?=
 =?us-ascii?Q?n2iXsrenhqz2fQyIbKORL5wUHpHS9TUq4c1mTChgy5TNBujF/RI9nmK3D2J9?=
 =?us-ascii?Q?6a+FIBabS3djrRjmJHu7tnKMrb9NGrt3YogU4YxOj9gf0Td9IJ36WgqHuONw?=
 =?us-ascii?Q?egPX8AbpzZyLlWGvgDo2QgHja2O52HLkj3CvzFCM423ZPoQiHLVwIkR62zgw?=
 =?us-ascii?Q?nqdU4Kyhac0iXHx+8ArePc6RdIKxcGqPBIomHI8ZQo7wpiigYBQLggvLpC31?=
 =?us-ascii?Q?6o7o/87sIUO4nW1G3BjakLTLrbo030ljxjQhijxbs6kvF1Bkq6x6DwV7xoDU?=
 =?us-ascii?Q?CTib1OXPLJGrBHigxHpNKS4SWLJORXJvWeg9MNLB?=
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: 
 9782af27-6bc6-4e20-0d76-08dd2f56e274
X-MS-Exchange-CrossTenant-originalarrivaltime: 07 Jan 2025 20:07:16.8632
 (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: 
 FqJGRNPCf8s6XNWwwjLT88ANFZCKkA6pzWPqMokoktLTWBaVjdA2yVwNQL8Fej19roq6AtuQuewfm3yfIV5+GsUki5unlSrgeIVxe3lvR0U=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM9PR04MB8413
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/DU0PR04MB941950CC9F69A0F3B0FE877B99112%40DU0PR04MB9419.eurprd04.prod.outlook.com>
Precedence: bulk

Thanks for the explanation, but what about the reported problem.
How can I force the view to use both indexes?


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Tuesday, January 7, 2025 3:52 PM
To: Zornoza Sanchez, Jose Blas <jbzornoza@sia.es>
Cc: Tomasz Szypowski <tomasz.szypowski@asseco.pl>; pgsql-sql@lists.postgres=
ql.org
Subject: Re: View performance with implicit cast

"Zornoza Sanchez, Jose Blas" <jbzornoza@sia.es> writes:
> Hello, in this case both index and view have the same name (test), try a =
different one...

Yeah.  If you try the example as-presented it fails immediately:

postgres=3D# create table foo (id int);
CREATE TABLE
postgres=3D# CREATE VIEW test AS SELECT * FROM foo; CREATE VIEW postgres=3D=
# CREATE INDEX test ON foo(id);
ERROR:  relation "test" already exists

because you can't put a view named test and an index named test into the sa=
me schema.  (They share the namespace of tables.)  What I think the OP migh=
t have done is something similar to

postgres=3D# create schema s1;
CREATE SCHEMA
postgres=3D# create schema s2;
CREATE SCHEMA
postgres=3D# set search_path to s1, s2;
SET
postgres=3D# create table s2.foo (id int); CREATE TABLE postgres=3D# CREATE=
 VIEW test AS SELECT * FROM foo; CREATE VIEW postgres=3D# CREATE INDEX test=
 ON foo(id); CREATE INDEX postgres=3D# DROP INDEX test;
ERROR:  "test" is not an index
HINT:  Use DROP VIEW to remove a view.

View test is in schema s1, because that's the default creation schema with =
this search_path setting.  But index test is in s2, because indexes are alw=
ays put in the same schema as their parent table.
So the CREATE INDEX doesn't fail.  But then the DROP searches the search_pa=
th, and the first "test" it finds is the view s1.test, so it complains.

			regards, tom lane