RE: Why is this doing a seq scan?

> Hmm. Have you VACUUM ANALYZED the tables? If so, what do
> you get from
> these queries:

Tom, thanks for the reply, and here is all the info you asked for.

> select attname,attdisbursion,s.*
> from pg_statistic s, pg_attribute a, pg_class c
> where starelid = c.oid and attrelid = c.oid and staattnum = attnum
> and relname = 'zips';

attname
|attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv
al|staloval |stahival
---------+-------------+--------+---------+-----+-----------+-------------+-
-----------+----------+--------
zip | -1| 93920| 1| 1066| 0|
7.13394e-07|01226 |00401 |Y1A6A1
state | 0.165522| 93920| 2| 1066| 0|
0.346728|ON |AB |YT
city | 0.00729095| 93920| 3| 1066| 0|
0.0322854|TORONTO | |ZWOLLE
lat | 0.00326189| 93920| 4| 672| 0|
0.0153651|51.05 |-123.176 |79.989
lon | 0.00326061| 93920| 5| 672| 0|
0.0153594|-114.083333 |-176.31005|144.445
bestbound| 0.997491| 93920| 6| 672| 0.998605| 0.00107366|2
|2 |98
(6 rows)

> select attname,attdisbursion,s.*
> from pg_statistic s, pg_attribute a, pg_class c
> where starelid = c.oid and attrelid = c.oid and staattnum = attnum
> and relname = 'atms';

attname
|attdisbursion|starelid|staattnum|staop|stanullfrac|stacommonfrac|stacommonv
al |staloval |stahival
----------------+-------------+--------+---------+-----+-----------+--------
-----+---------------+------------------------------+----------------
terminal | -1| 50904| 1| 97| 0|
0.000433463|6000 |55 |9433
district | 0.0679035| 50904| 2| 664| 0|
0.192024|ARCO California|ARCO Arizona |Western New York
name | 0.000261431| 50904| 3| 664| 0|
0.00130039|Gateway Center |11th & Conger |Zionsville
address | 0.000261431| 50904| 4| 664| 0|
0.00130039|215 Tecumseh Rd|"402 E Yakima Ave, Suite 1400"|Windham Mall
city | 0.00522279| 50904| 5| 664| 0|
0.0238405|Seattle |Aberdeen |Zionsville
state | 0.0687854| 50904| 6| 1058| 0|
0.193758|CA |AK |WA
zip | 0.000614214| 50904| 7| 664| 0|
0.00303424|92392 | |99901
access | 0.385091| 50904| 8| 664| 0|
0.579974|WU | |WU
function | 0.396416| 50904| 9| 664| 0|
0.589944|FF |CD |FF
location | 0.414461| 50904| 10| 664| 0|
0.605548|BR |BR |Rem
language | 0.431861| 50904| 11| 664| 0|
0.620286|E | |E
restricted_hours| 0.886758| 50904| 12| 664| 0|
0.939749|FALSE |FALSE |TRUE
seasonal | 0.994812| 50904| 13| 664| 0|
0.997399|FALSE |FALSE |TRUE
stamps | 0.621877| 50904| 14| 664| 0|
0.746857|FALSE |FALSE |TRUE
(14 rows)

> Also it would be useful to see the full declarations of the tables
> and their indexes; I'm wondering what datatype the zip columns are,
> for example.

Table = atms
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| terminal | int4 |
4 |
| district | text |
var |
| name | text |
var |
| address | text |
var |
| city | text |
var |
| state | char() |
2 |
| zip | text |
var |
| access | text |
var |
| function | text |
var |
| location | text |
var |
| language | text |
var |
| restricted_hours | text |
var |
| seasonal | text |
var |
| stamps | text |
var |
+----------------------------------+----------------------------------+-----
--+
Index: atms_zip

thirdfed=> \d zips
Table = zips
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| zip | varchar() |
10 |
| state | varchar() |
3 |
| city | varchar() |
100 |
| lat | float8 |
8 |
| lon | float8 |
8 |
| bestbound | float8 |
8 |
+----------------------------------+----------------------------------+-----
--+
Indices: zips_latindex
zips_lonindex
zips_pkey

thirdfed=> \d zips_pkey
Table = zips_pkey
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| zip | varchar() |
10 |
+----------------------------------+----------------------------------+-----
--+
thirdfed=> \d atms_zip
Table = atms_zip
+----------------------------------+----------------------------------+-----
--+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-----
--+
| zip | text |
var |
+----------------------------------+----------------------------------+-----
--+

Thanks,
Bryan