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.31rows=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
