From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomasz Szypowski <tomasz.szypowski@asseco.pl>
cc: "Zornoza Sanchez, Jose Blas" <jbzornoza@sia.es>,
        "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: View performance with implicit cast
In-reply-to: 
 <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>
 <DU0PR04MB941950CC9F69A0F3B0FE877B99112@DU0PR04MB9419.eurprd04.prod.outlook.com>
Comments: In-reply-to Tomasz Szypowski <tomasz.szypowski@asseco.pl>
	message dated "Tue, 07 Jan 2025 20:07:16 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1508459.1736282080.1@sss.pgh.pa.us>
Date: Tue, 07 Jan 2025 15:34:40 -0500
Message-ID: <1508460.1736282080@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1508460.1736282080%40sss.pgh.pa.us>
Precedence: bulk

Tomasz Szypowski <tomasz.szypowski@asseco.pl> writes:
> Thanks for the explanation, but what about the reported problem.
> How can I force the view to use both indexes?

You can't, because the indexes are not on the same expressions
appearing in the view.  Your outer WHERE clause constrains those
expressions, not the underlying table columns.

If it's impractical to make the underlying tables share the same
column type, you would need to do something like

	create view v_test as
	select date_1 from test1
	union all
	select date_2::timestamp from test2;

	create index on test2 ((date_2::timestamp));

Consider the extra index as your penance for not having thought
harder about data type choices to begin with.

			regards, tom lane