From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Josh Berkus" <josh(at)agliodbs(dot)com>, Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Planner matching constants across tables in a |
Date: | 2003-03-05 19:31:44 |
Message-ID: | 200303051931.44701.dev@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-03-05 19:37:39 | Re: Batch copying of databases |
Previous Message | Richard Huxton | 2003-03-05 19:25:43 | Re: Planner matching constants across tables in a |