Re: Planner matching constants across tables in a

On Wednesday 05 Mar 2003 7:00 pm, Josh Berkus wrote:
> You might improve your performance overall if you cast the constant to
> tel_num before doing the comparison in the query.

Stranger and stranger...

richardh=# CREATE DOMAIN intdom int4;
richardh=# CREATE DOMAIN textdom text;
richardh=# CREATE TABLE domtest (a intdom, b textdom);
richardh=# CREATE INDEX domtest_a_idx ON domtest (a);
richardh=# CREATE INDEX domtest_b_idx ON domtest (b);
richardh=# INSERT INTO domtest VALUES (1,'aaa');
richardh=# INSERT INTO domtest VALUES (2,'bbb');
richardh=# INSERT INTO domtest VALUES (3,'ccc');

richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=1::intdom;
-------------------------------------------------------------------------------------------------
 Seq Scan on domtest  (cost=0.00..22.50 rows=5 width=36) (actual
time=0.08..0.11 rows=1 loops=1)
   Filter: ((a)::oid = 1::oid)

richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=1::int4;
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using domtest_a_idx on domtest  (cost=0.00..17.07 rows=5 width=36)
(actual time=0.09..0.11 rows=1 loops=1)
   Index Cond: ((a)::integer = 1)

richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b='aaa'::textdom;
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using domtest_b_idx on domtest  (cost=0.00..17.07 rows=5 width=36)
(actual time=0.09..0.11 rows=1 loops=1)
   Index Cond: ((b)::text = 'aaa'::text)

richardh=# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b='aaa'::text;
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using domtest_b_idx on domtest  (cost=0.00..17.07 rows=5 width=36)
(actual time=0.10..0.12 rows=1 loops=1)
   Index Cond: ((b)::text = 'aaa'::text)

Can't think why we're getting casts to type "oid" in the first example - I'd
have thought int4 would be the default. I'm guessing the text domain always
works because that's the default cast.

--
  Richard Huxton