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@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
