public inbox for [email protected]
help / color / mirror / Atom feedFrom: Zornoza Sanchez, Jose Blas <[email protected]>
To: Tomasz Szypowski <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: View performance with implicit cast
Date: Tue, 7 Jan 2025 07:43:48 +0000
Message-ID: <PR3P193MB073140BB6374E47E7E6EB87CCC112@PR3P193MB0731.EURP193.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com>
References: <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
<[email protected]>
<DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com>
Hello, in this case both index and view have the same name (test), try a different one...
-----Mensaje original-----
De: Tomasz Szypowski <[email protected]>
Enviado el: domingo, 5 de enero de 2025 23:45
Para: Tom Lane <[email protected]>
CC: [email protected]
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 <[email protected]>
Sent: Saturday, January 4, 2025 7:37 PM
To: Tomasz Szypowski <[email protected]>
Cc: [email protected]
Subject: Re: View performance with implicit cast
[Nie otrzymujesz cz?sto wiadomo?ci e-mail z [email protected]. Dowiedz si?, dlaczego jest to wa?ne, na stronie https://aka.ms/LearnAboutSenderIdentification ]
Tomasz Szypowski <[email protected]> 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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: RE: View performance with implicit cast
In-Reply-To: <PR3P193MB073140BB6374E47E7E6EB87CCC112@PR3P193MB0731.EURP193.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox