X-Original-To: pgsql-performance@postgresql.org Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by postgresql.org (Postfix) with ESMTP id A992A476302 for ; Wed, 5 Mar 2003 14:26:36 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 18qeXE-000ATP-0X; Wed, 05 Mar 2003 19:26:36 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 6A4901797B; Wed, 5 Mar 2003 19:26:35 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id C6A071797A; Wed, 5 Mar 2003 19:26:34 +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:25:43 +0000 User-Agent: KMail/1.4.3 Cc: Greg Stark , References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303051925.43178.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200303/33 X-Sequence-Number: 1331 On Wednesday 05 Mar 2003 7:00 pm, Josh Berkus wrote: > Richard, > > > A domain defined as varchar() actually - which is why it's not using > > an index, > > but that's neither here nor there regarding the constant issue. > > You might improve your performance overall if you cast the constant to > tel_num before doing the comparison in the query. Right now, the > parser is casting the whole column to text instead, because it can't > tell that the constant you supply is a valid tel_num. That's what I thought, but... rms=3D> EXPLAIN ANALYSE SELECT * FROM line WHERE telno=3D'0912345 0004'::te= l_num; QUERY PLAN ---------------------------------------------------------------------------= ------------------- Seq Scan on line (cost=3D0.00..20.50 rows=3D1 width=3D28) (actual time=3D= 0.10..5.28=20 rows=3D1 loops=3D1) Filter: ((telno)::text =3D ('0912345 0004'::character varying)::text) Total runtime: 5.43 msec rms=3D> EXPLAIN ANALYSE SELECT * FROM line WHERE telno=3D'0912345 0004'::va= rchar; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------- Index Scan using line_pkey on line (cost=3D0.00..5.78 rows=3D1 width=3D28= ) (actual=20 time=3D14.03..14.03 rows=3D1 loops=3D1) Index Cond: ((telno)::character varying =3D '0912345 0004'::character=20 varying) Total runtime: 14.28 msec Ignoring the times (fake data on my test box) it seems like there's an issu= e=20 in comparing against DOMAIN defined types. Or maybe it's in the index=20 definition, although I don't know how to find out the type of an index. --=20 Richard Huxton