RE: View performance with implicit cast

Thanks for the explanation, but what about the reported problem.
How can I force the view to use both indexes?

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, January 7, 2025 3:52 PM
To: Zornoza Sanchez, Jose Blas <jbzornoza(at)sia(dot)es>
Cc: Tomasz Szypowski <tomasz(dot)szypowski(at)asseco(dot)pl>; pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Re: View performance with implicit cast

"Zornoza Sanchez, Jose Blas" <jbzornoza(at)sia(dot)es> writes:
> Hello, in this case both index and view have the same name (test), try a different one...

Yeah. If you try the example as-presented it fails immediately:

postgres=# create table foo (id int);
CREATE TABLE
postgres=# CREATE VIEW test AS SELECT * FROM foo; CREATE VIEW postgres=# CREATE INDEX test ON foo(id);
ERROR: relation "test" already exists

because you can't put a view named test and an index named test into the same schema. (They share the namespace of tables.) What I think the OP might have done is something similar to

postgres=# create schema s1;
CREATE SCHEMA
postgres=# create schema s2;
CREATE SCHEMA
postgres=# set search_path to s1, s2;
SET
postgres=# create table s2.foo (id int); CREATE TABLE postgres=# CREATE VIEW test AS SELECT * FROM foo; CREATE VIEW postgres=# CREATE INDEX test ON foo(id); CREATE INDEX postgres=# DROP INDEX test;
ERROR: "test" is not an index
HINT: Use DROP VIEW to remove a view.

View test is in schema s1, because that's the default creation schema with this search_path setting. But index test is in s2, because indexes are always put in the same schema as their parent table.
So the CREATE INDEX doesn't fail. But then the DROP searches the search_path, and the first "test" it finds is the view s1.test, so it complains.

regards, tom lane