Re: 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