Thomas Reiss <thomas.re...@dalibo.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
