Re: Optimizing a query - Mailing list pgsql-performance

Have you run vacuum/analyze on the table?

--
  Husam

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of James Cloos
Sent: Wednesday, December 13, 2006 10:48 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 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@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may
containinformation that is legally privileged.  If you are not the addressee, or the person responsible for delivering
itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is
strictlyprohibited.  If you have received this message by mistake, please immediately notify us by replying to the
messageand delete the original message immediately thereafter. 

Thank you.

                                   FADLD Tag
**********************************************************************