From: James Cloos <cloos@jhcloos.com>
To: pgsql-performance@postgresql.org
Subject: Optimizing a query
Date: Wed, 13 Dec 2006 13:48:10 -0500
Message-ID: <m31wn3lji6.fsf@lugabout.jhcloos.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cloos@jhcloos.com
Subject: Re: Optimizing a query
In-Reply-To: <m31wn3lji6.fsf@lugabout.jhcloos.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox