Skip site navigation (1) Skip section navigation (2)

Optimizing a query

From: James Cloos <cloos(at)jhcloos(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizing a query
Date: 2006-12-13 18:48:10
Message-ID: m31wn3lji6.fsf@lugabout.jhcloos.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: Bucky JordanDate: 2006-12-13 18:49:52
Subject: Re: New to PostgreSQL, performance considerations
Previous:From: Tom LaneDate: 2006-12-13 18:47:16
Subject: Re: Slow update with simple query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group