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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, January 4, 2025 7:37 PM
To: Tomasz Szypowski <tomasz(dot)szypowski(at)asseco(dot)pl>
Cc: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: View performance with implicit cast

[Nie otrzymujesz cz?sto wiadomo?ci e-mail z tgl(at)sss(dot)pgh(dot)pa(dot)us(dot) Dowiedz si?, dlaczego jest to wa?ne, na stronie https://aka.ms/LearnAboutSenderIdentification ]

Tomasz Szypowski <tomasz(dot)szypowski(at)asseco(dot)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