Optimizing a query

I've currently got this table:

,----
| n=# \d nanpa
| Table "public.nanpa"
| Column | Type | Modifiers
| ------------+--------------+-----------
| state | character(2) |
| npa | character(3) | not null
| nxx | character(3) | not null
| ocn | character(4) |
| company | text |
| ratecenter | text |
| switch | text |
| effective | date |
| use | character(2) | not null
| assign | date |
| ig | character(1) |
| Indexes:
| "nanpa_pkey" PRIMARY KEY, btree (npa, nxx) CLUSTER
`----

and was doing queries of the form:

,----
| select * from nanpa where npa=775 and nxx=413;
`----

where were quite slow. Explain showed that it was doing sequential
scans even though the primary key contained the two term I was
selecting on.

Today, looking at it again in prep to this post, I noticed that the
numbers were being converted to ::text, and a quick test showed that
queries of the form:

,----
| select * from nanpa where npa=775::bpchar and nxx=413::bpchar;
`----

used the index.

I specified char(3) when I created the table simple because npa and
nxx are defined as three-character strings. Tagging the queies is
a pain, especially as I often do queries of that form in psql(1).

(Incidently, there are multiple similar tables, also keyed on
(npa,nxx), which show the same problem. The nanpa table above is
just a good example.)

Should I convert the columns to text? Or create an additional index
that expects ::text args? (If so, how?)

Or is there some other way to ensure the indices get used w/o having
to tag data in the queries?

Thanks,

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6