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
