From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Thomas Reiss <thomas(dot)reiss(at)dalibo(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Casting issues with domains |
Date: | 2014-12-10 18:41:27 |
Message-ID: | 889339290.4895779.1418236887637.JavaMail.yahoo@jws100160.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2014-12-10 20:24:57 | Re: GSSAPI, SSPI - include_realm default |
Previous Message | Tom Lane | 2014-12-10 18:36:48 | Re: Final Patch for GROUPING SETS |