RE: View performance with implicit cast

Hello, in this case both index and view have the same name (test), try a different one...

-----Mensaje original-----
De: Tomasz Szypowski <tomasz(dot)szypowski(at)asseco(dot)pl>
Enviado el: domingo, 5 de enero de 2025 23:45
Para: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
CC: pgsql-sql(at)lists(dot)postgresql(dot)org
Asunto: 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

________________________________

Este correo electrónico y, en su caso, cualquier fichero anexo al mismo, contiene información de carácter confidencial exclusivamente dirigida a su destinatario o destinatarios. Si no es vd. el destinatario indicado, queda notificado que la lectura, utilización, divulgación y/o copia sin autorización está prohibida en virtud de la legislación vigente. En el caso de haber recibido este correo electrónico por error, se ruega notificar inmediatamente esta circunstancia mediante reenvío a la dirección electrónica del remitente.
Evite imprimir este mensaje si no es estrictamente necesario.

This email and any file attached to it (when applicable) contain(s) confidential information that is exclusively addressed to its recipient(s). If you are not the indicated recipient, you are informed that reading, using, disseminating and/or copying it without authorisation is forbidden in accordance with the legislation in effect. If you have received this email by mistake, please immediately notify the sender of the situation by resending it to their email address.
Avoid printing this message if it is not absolutely necessary.