X-Original-To: pgsql-performance@postgresql.org Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by postgresql.org (Postfix) with ESMTP id 3DCE24762B8 for ; Wed, 5 Mar 2003 11:12:59 -0500 (EST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 18qbVv-0004xQ-0d; Wed, 05 Mar 2003 16:13:03 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 42DCC1784E; Wed, 5 Mar 2003 16:13:02 +0000 (GMT) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id 8E2C31646A; Wed, 5 Mar 2003 16:13:01 +0000 (GMT) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Greg Stark Subject: Re: Planner matching constants across tables in a join Date: Wed, 5 Mar 2003 16:12:09 +0000 User-Agent: KMail/1.4.3 Cc: Greg Stark , References: <200303051113.14320.dev@archonet.com> <200303051424.12351.dev@archonet.com> <87llzt97ra.fsf@stark.dyndns.tv> In-Reply-To: <87llzt97ra.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200303051612.09558.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Archive-Number: 200303/31 X-Sequence-Number: 1329 On Wednesday 05 Mar 2003 3:02 pm, Greg Stark wrote: > Richard Huxton writes: > > Filter: ((line_id)::text =3D '0912345 0004'::text) > > So I think this means that line_id is being casted to "text". Though I'm > not clear why it would be choosing "text" for the constant if line_id > wasn't text to begin with. A domain defined as varchar() actually - which is why it's not using an ind= ex,=20 but that's neither here nor there regarding the constant issue. > In any case my plans here look like: > > Filter: (aa =3D 'x'::text) > > so it looks like there's something extra going on in your plan. > > what does your table definition look like? rms=3D> \d campaign Table "rms.campaign" Column | Type | Modifiers ----------+-----------+----------- id | integer | not null title | item_name | cam_from | date | cam_to | date | owner | integer | Indexes: campaign_pkey primary key btree (id), campaign_from_idx btree (cam_from), campaign_to_idx btree (cam_to) rms=3D> \d campaign_items Table "rms.campaign_items" Column | Type | Modifiers -------------+---------+----------- cam_id | integer | not null line_id | tel_num | not null prod_id | integer | not null chg_per_min | integer | rev_per_min | integer | Indexes: campaign_items_pkey primary key btree (cam_id, line_id, prod_id), cam_item_line_idx btree (line_id) Foreign Key constraints: $1 FOREIGN KEY (cam_id) REFERENCES campaign(id) ON= =20 UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (line_id) REFERENCES line(telno) ON= =20 UPDATE NO ACTION ON DELETE NO ACTION, $3 FOREIGN KEY (prod_id) REFERENCES product(id) ON= =20 UPDATE NO ACTION ON DELETE NO ACTION rms=3D> \d activity Table "rms.activity" Column | Type | Modifiers ------------+-----------------------------+----------- line_id | tel_num | not null start_time | timestamp without time zone | not null call_dur | integer | Indexes: activity_pkey primary key btree (line_id, start_time), activity_start_idx btree (start_time) Foreign Key constraints: $1 FOREIGN KEY (line_id) REFERENCES line(telno) ON= =20 UPDATE NO ACTION ON DELETE NO ACTION --=20 Richard Huxton