Have you run vacuum/analyze on the table? -- Husam -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of James Cloos Sent: Wednesday, December 13, 2006 10:48 AM To: pgsql-performance@xxxxxxxxxxxxxx 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@xxxxxxxxxxx> 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 contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag **********************************************************************
