public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tomasz Szypowski <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: View performance with implicit cast
Date: Sun, 5 Jan 2025 22:45:14 +0000
Message-ID: <DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
	<[email protected]>

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

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]
  Subject: RE: View performance with implicit cast
  In-Reply-To: <DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.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