RE: View performance with implicit cast

Thanks

So how can i create the view, with different date data types (date, timestamp), so that both indexes would be involved. I see only the solution with index with casted value (create index ... on ...(cast(... as date))), but maybe 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> 
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/LearnAboutSenderIdentification ]

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