Skip site navigation (1) Skip section navigation (2)

Yet another 'why does it not use my index' question.

From: "Ryan" <pgsql-performance(at)seahat(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Yet another 'why does it not use my index' question.
Date: 2003-05-07 14:11:49
Message-ID: 11207.65.102.128.233.1052316709.squirrel@fordparts.com (view raw or flat)
Thread:
Lists: pgsql-performance
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=# explain analyze select * from zip where zip in
 (select option_value from client_options where option_name = 'ZIP_CODE' );
                               QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on zip  (cost=0.00..206467.85 rows=38028 width=112)
                  (actual time=58.45..4676.76 rows=8 loops=1)
   Filter: (subplan)
   SubPlan
     ->  Seq Scan on client_options  (cost=0.00..5.36 rows=3 width=14)
                                (actual time=0.02..0.05 rows=3 loops=76056)
           Filter: (option_name = 'ZIP_CODE'::text)
 Total runtime: 4676.87 msec

Or even:

midas=# explain analyze select * from zip z, client_options c where
c.option_name = 'ZIP_CODE' and c.option_value = z.zip;
                              QUERY PLAN
---------------------------------------------------------------------------
 Nested Loop  (cost=0.00..9915.14 rows=10 width=148)
              (actual time=26.63..2864.01 rows=8 loops=1)
   Join Filter: ("outer".option_value = ("inner".zip)::text)
   ->  Seq Scan on client_options c  (cost=0.00..5.36 rows=3 width=36)
                                    (actual time=0.25..0.34 rows=3 loops=1)
         Filter: (option_name = 'ZIP_CODE'::text)
   ->  Seq Scan on zip z  (cost=0.00..2352.56 rows=76056 width=112)
                          (actual time=0.07..809.19 rows=76056 loops=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


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-05-07 14:19:25
Subject: Re: [PERFORM] Hypothetical suggestions for planner, indexing
Previous:From: Hannu KrosingDate: 2003-05-07 13:40:06
Subject: Re: An unresolved performance problem.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group