Re: Optimizing a query

Your nap and nxx columns have character datatype, so you should use
quotes. Try:

explain analyze select * from nanpa where npa='775' and
nxx='473';

If that does not work, you could try to influence the planner's
execution plan to favor index scans over sequential scan by tweaking a
couple of the postgres parameters, particularly, the
effective_cache_size. This parameter primarily set the planner's
estimates of the relative likelihood of a particular table or index
being in memory, and will thus have a significant effect on whether the
planner chooses indexes over seqscans. Tweaking such parameters are
usually done as a last resort.

--
Husam

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of James Cloos
Sent: Wednesday, December 13, 2006 2:35 PM
To: Tomeh, Husam
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Optimizing a query

>>>>> "Husam" == Tomeh, Husam <htomeh(at)firstam(dot)com> writes:

Husam> Have you run vacuum/analyze on the table?

Yes, back when I first noticed how slow it was.
It did not make any difference.

explain analyze says:

,----
| n=# explain analyse select * from nanpa where npa=775 and nxx=473;
| QUERY PLAN

|
------------------------------------------------------------------------
--------------------------------
| Seq Scan on nanpa (cost=0.00..5344.60 rows=4 width=105) (actual
time=371.718..516.816 rows=1 loops=1)
| Filter: (((npa)::text = '775'::text) AND ((nxx)::text =
'473'::text))
| Total runtime: 516.909 ms
| (3 rows)
`----

vs:

,----
| n=# explain analyse select * from nanpa where npa=775::char and
nxx=473::char;
| QUERY PLAN

|
------------------------------------------------------------------------
----------------------------------------------
| Index Scan using nanpa_pkey on nanpa (cost=0.00..4.33 rows=1
width=105) (actual time=64.831..64.831 rows=0 loops=1)
| Index Cond: ((npa = '7'::bpchar) AND (nxx = '4'::bpchar))
| Total runtime: 64.927 ms
| (3 rows)
`----

BTW, I forgot to mention I'm at 8.1.4 on that box.

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq
**********************************************************************
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
**********************************************************************