RE: View performance with implicit cast - Mailing list pgsql-sql

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

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



________________________________

Este correo electrónico y, en su caso, cualquier fichero anexo al mismo, contiene información de carácter confidencial
exclusivamentedirigida a su destinatario o destinatarios. Si no es vd. el destinatario indicado, queda notificado que
lalectura, utilización, divulgación y/o copia sin autorización está prohibida en virtud de la legislación vigente. En
elcaso de haber recibido este correo electrónico por error, se ruega notificar inmediatamente esta circunstancia
mediantereenví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
addressedto its recipient(s). If you are not the indicated recipient, you are informed that reading, using,
disseminatingand/or copying it without authorisation is forbidden in accordance with the legislation in effect. If you
havereceived this email by mistake, please immediately notify the sender of the situation by resending it to their
emailaddress.
 
Avoid printing this message if it is not absolutely necessary.