Hello all,
We experienced some casting issues with domains. We experienced the problem while querying the information_schema btw, but here is a simpler test case : postgres=# create table test1 (a text); CREATE TABLE postgres=# insert into test1 select generate_series(1,100000); INSERT 0 100000 postgres=# create index idx1 on test1(a); CREATE INDEX postgres=# analyze test1 ; ANALYZE; postgres=# explain select * from test1 where a = 'toto'; QUERY PLAN ----------------------------------------------------------------------- Index Only Scan using idx1 on test1 (cost=0.29..8.31 rows=1 width=5) Index Cond: (a = 'toto'::text) (2 lignes) Now we create a tstdom domain and cast the a column to tstdom in the view definition : postgres=# create domain tstdom as text; CREATE DOMAIN postgres=# create view test2 as select a::tstdom from test1 ; CREATE VIEW postgres=# explain select * from test2 where a='toto'; QUERY PLAN ---------------------------------------------------------- Seq Scan on test1 (cost=0.00..1693.00 rows=500 width=5) Filter: (((a)::tstdom)::text = 'toto'::text) (2 lignes) As you can see, a is casted to tstdom then again to text. This casts prevents the optimizer to choose an index scan to retrieve the data. The casts are however strictly equivalent and should be not prevent the optimizer to use indexes. Also, the same problem appears in the information_schema views, as every object names are casted to information_schema.sql_identifier. Even if this domain is declared as name, no index will be used because of this cast. Shouldn't the planner simplify the casts when it's possible ? Regards, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers