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.
Isee only the solution with index with casted value (create index ... on ...(cast(... as date))), but maybe it is a
bettersolution? 

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