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@xxxxxxxxxxx> OpenPGP: 1024D/ED7DAEA6
