X-Original-To: pgsql-performance@postgresql.org Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by postgresql.org (Postfix) with ESMTP id 4D64F475CE5 for ; Wed, 5 Mar 2003 14:32:38 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2) id 18qed3-0005sr-0Z; Wed, 05 Mar 2003 19:32:38 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 25D6C1797B; Wed, 5 Mar 2003 19:32:37 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 7C18017978; Wed, 5 Mar 2003 19:32:36 +0000 (GMT) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: "Josh Berkus" , Greg Stark Subject: Re: Planner matching constants across tables in a Date: Wed, 5 Mar 2003 19:31:44 +0000 User-Agent: KMail/1.4.3 Cc: References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303051931.44701.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200303/34 X-Sequence-Number: 1332 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.=20 Stranger and stranger... richardh=3D# CREATE DOMAIN intdom int4; richardh=3D# CREATE DOMAIN textdom text; richardh=3D# CREATE TABLE domtest (a intdom, b textdom); richardh=3D# CREATE INDEX domtest_a_idx ON domtest (a); richardh=3D# CREATE INDEX domtest_b_idx ON domtest (b); richardh=3D# INSERT INTO domtest VALUES (1,'aaa'); richardh=3D# INSERT INTO domtest VALUES (2,'bbb'); richardh=3D# INSERT INTO domtest VALUES (3,'ccc'); richardh=3D# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=3D1::intdom; ---------------------------------------------------------------------------= ---------------------- Seq Scan on domtest (cost=3D0.00..22.50 rows=3D5 width=3D36) (actual=20 time=3D0.08..0.11 rows=3D1 loops=3D1) Filter: ((a)::oid =3D 1::oid) richardh=3D# EXPLAIN ANALYSE SELECT * FROM domtest WHERE a=3D1::int4; ---------------------------------------------------------------------------= -------------------------------------------- Index Scan using domtest_a_idx on domtest (cost=3D0.00..17.07 rows=3D5 wi= dth=3D36)=20 (actual time=3D0.09..0.11 rows=3D1 loops=3D1) Index Cond: ((a)::integer =3D 1) richardh=3D# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b=3D'aaa'::textdom; ---------------------------------------------------------------------------= -------------------------------------------- Index Scan using domtest_b_idx on domtest (cost=3D0.00..17.07 rows=3D5 wi= dth=3D36)=20 (actual time=3D0.09..0.11 rows=3D1 loops=3D1) Index Cond: ((b)::text =3D 'aaa'::text) richardh=3D# EXPLAIN ANALYSE SELECT * FROM domtest WHERE b=3D'aaa'::text; ---------------------------------------------------------------------------= -------------------------------------------- Index Scan using domtest_b_idx on domtest (cost=3D0.00..17.07 rows=3D5 wi= dth=3D36)=20 (actual time=3D0.10..0.12 rows=3D1 loops=3D1) Index Cond: ((b)::text =3D 'aaa'::text) Can't think why we're getting casts to type "oid" in the first example - I'= d=20 have thought int4 would be the default. I'm guessing the text domain always= =20 works because that's the default cast. --=20 Richard Huxton