X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id ABF7B4763CE for ; Wed, 7 May 2003 12:07:27 -0400 (EDT) Received: from t-9.net (unknown [64.246.36.43]) by postgresql.org (Postfix) with SMTP id E6CD64764FB for ; Wed, 7 May 2003 12:07:21 -0400 (EDT) Received: (qmail 5479 invoked by uid 0); 7 May 2003 14:11:49 -0000 Received: from localhost (HELO seahat.com) (127.0.0.1) by localhost with SMTP; 7 May 2003 14:11:49 -0000 Received: from 65.102.128.233 (SquirrelMail authenticated user snubber@seahat.com) by fordparts.com with HTTP; Wed, 7 May 2003 09:11:49 -0500 (CDT) Message-ID: <11207.65.102.128.233.1052316709.squirrel@fordparts.com> Date: Wed, 7 May 2003 09:11:49 -0500 (CDT) Subject: Yet another 'why does it not use my index' question. From: "Ryan" To: X-Priority: 3 Importance: Normal X-Mailer: SquirrelMail (version 1.2.8) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-5.7 required=5.0 tests=BAYES_01,MSG_ID_ADDED_BY_MTA_3 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/83 X-Sequence-Number: 1862 Ok, I have two tables (Postgresql 7.3.2 on Debian): Table "public.zip" Column | Type | Modifiers ------------+-----------------------+----------- zip | character varying(5) | city | character varying(25) | county | character varying(30) | countyfips | character varying(5) | state_full | character varying(30) | state | character varying(2) | citytype | character(1) | zipcodetyp | character(1) | areacode | character varying(3) | timezone | character varying(10) | dst | character(1) | latitude | double precision | longitude | double precision | country | character varying(10) | Indexes: zip_idx btree (zip) Table "public.client_options" Column | Type | Modifiers --------------+--------+----------- client_id | bigint | not null option_name | text | not null option_value | text | not null Foreign Key constraints: [...omitted...] I wanted to do the following: midas=3D# explain analyze select * from zip where zip in (select option_value from client_options where option_name =3D 'ZIP_CODE' = ); QUERY PLAN --------------------------------------------------------------------------- Seq Scan on zip (cost=3D0.00..206467.85 rows=3D38028 width=3D112) (actual time=3D58.45..4676.76 rows=3D8 loops=3D1) Filter: (subplan) SubPlan -> Seq Scan on client_options (cost=3D0.00..5.36 rows=3D3 width=3D14) (actual time=3D0.02..0.05 rows=3D3 loops=3D= 76056) Filter: (option_name =3D 'ZIP_CODE'::text) Total runtime: 4676.87 msec Or even: midas=3D# explain analyze select * from zip z, client_options c where c.option_name =3D 'ZIP_CODE' and c.option_value =3D z.zip; QUERY PLAN --------------------------------------------------------------------------- Nested Loop (cost=3D0.00..9915.14 rows=3D10 width=3D148) (actual time=3D26.63..2864.01 rows=3D8 loops=3D1) Join Filter: ("outer".option_value =3D ("inner".zip)::text) -> Seq Scan on client_options c (cost=3D0.00..5.36 rows=3D3 width=3D36) (actual time=3D0.25..0.34 rows=3D3 loop= s=3D1) Filter: (option_name =3D 'ZIP_CODE'::text) -> Seq Scan on zip z (cost=3D0.00..2352.56 rows=3D76056 width=3D112) (actual time=3D0.07..809.19 rows=3D76056 loops=3D= 3) Total runtime: 2864.16 msec If I wanted to do select the zip codes out of the client_options and then select the zipcodes seperately, I would be looking at times of .14 msec and 222.82 msec respectively. Oh, and yes, I have done a vacuum analyze. (the reason I'm trying to join these tables is to get longitude and latitude coordinates to use with the earthdistance <@> operator, it just takes entirely too long) What am I doing wrong? Ryan