Re: Casting issues with domains

Thomas Reiss <thomas(dot)reiss(at)dalibo(dot)com> wrote:

> 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.

You can create an index to be used for searching using the domain.
Following the steps in your example, you can run this:

postgres=# create index idx2 on test1 ((a::tstdom));
CREATE INDEX
postgres=# vacuum analyze test1;
VACUUM
postgres=# explain select * from test2 where a='toto';
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx2 on test1 (cost=0.29..8.31 rows=1 width=5)
Index Cond: (((a)::tstdom)::text = 'toto'::text)
(2 rows)

It's even easier if "a" is defined to be a member of the domain in
the original table:

postgres=# create domain tstdom as text;
CREATE DOMAIN
postgres=# create table test1 (a tstdom);
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 rows)

postgres=# create view test2 as select a::tstdom from test1 ;
CREATE VIEW
postgres=# explain select * from test2 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 rows)

It's kinda hard for me to visualize where it makes sense to define
the original table column as the bare type but use a domain when
referencing it in the view.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company