I have the following schema which I have set up, and I have inserted a
bunch of entries into it:
create domain contact_id as integer;
create sequence contact_seq;
create domain street_address as character varying(64);
create domain name as character varying(64);
create domain country as character(2);
create domain telno as character varying(17);
create domain extension as character varying(12);
create domain public_key as character varying(64);
create table contact (
id contact_id unique not null default nextval('contact_seq'),
public_key public_key unique not null,
name character varying (64),
org character varying (64),
street1 street_address,
street2 street_address,
city character varying(30),
region character(2),
country country,
postcode character varying(15),
voice telno,
voice_ext extension,
fax telno,
fax_ext extension,
cell telno,
email character varying(64),
created_on timestamp with time zone default now(),
updated_on timestamp with time zone default now()
);
performance=# explain analyze select * from contact where country::country
= 'AD'::character(2);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using contact_country_idx on contact (cost=0.00..1405.24
rows=360 width=302) (actual time=0.07..5.30 rows=344 loops=1)
Index Cond: ((country)::bpchar = 'AD'::bpchar)
Total runtime: 5.72 msec
(3 rows)
I do the same query using the actual name of the domain, and get the
following:
performance=# explain analyze select * from contact where country::country
= 'AD'::country;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on contact (cost=0.00..8181.81 rows=360 width=302) (actual
time=0.04..825.48 rows=344 loops=1)
Filter: ((country)::text = 'AD'::text)
Total runtime: 825.85 msec
(3 rows)
Apparently the filter transforms ::country into ::text, essentially losing
the domain information, and destroying the ability to detect the index.
I was a little disappointed that
explain analyze select * from contact where country = 'AD';
didn't do well; the value of DOMAINS is seriously injured if their
metadata gets lost, for optimization purposes.
Version 7.3.3, FYI... It's not inconceivable that this might have changed
in 7.4, which would strengthen the argument that DOMAINs didn't become
useful 'til 7.4...
[Rummaging around for 7.4 instance...]
performance=# explain analyze select * from contact where country =
'AD'::country;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using contact_country_idx on contact (cost=0.00..547.57
rows=291 width=516) (actual time=0.24..33.88 rows=143 loops=1)
Index Cond: ((country)::bpchar = (('AD'::bpchar)::country)::bpchar)
Total runtime: 34.43 msec
(3 rows)
Looks like that IS the case; in fact, it gets that same plan even if I
don't specify ::country on the country string...
This is obviously something that has changed _big time_ betwixt 7.3 and
7.4...
--
(reverse (concatenate 'string "ofni.smrytrebil@" "enworbbc"))
<http://dev6.int.libertyrms.info/>
Christopher Browne
(416) 646 3304 x124 (land)
