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@sss.pgh.pa.us>
Sent: Tuesday, January 7, 2025 3:52 PM
To: Zornoza Sanchez, Jose Blas <jbzornoza@sia.es>
Cc: Tomasz Szypowski <tomasz.szypowski@asseco.pl>; pgsql-sql@lists.postgresql.org
Subject: Re: View performance with implicit cast
"Zornoza Sanchez, Jose Blas" <jbzornoza@sia.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
ins2, 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
views1.test, so it complains.
regards, tom lane
