From: James Cloos <cloos@jhcloos.com>
To: pgsql-performance@postgresql.org
Subject: Optimizing a query
Copyright: Copyright 2006 James Cloos
Date: Wed, 13 Dec 2006 13:48:10 -0500
Message-ID: <m31wn3lji6.fsf@lugabout.jhcloos.org>
Lines: 61
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/23.0.0 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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