From: James Cloos <cloos@jhcloos.com>
To: Tomeh\, Husam <htomeh@firstam.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing a query
Date: Wed, 13 Dec 2006 17:35:26 -0500
Message-ID: <m3psanjuey.fsf@lugabout.jhcloos.org> (raw)
In-Reply-To: <F1B0F9305B343E43A1C3EECE48B853D509311C@CITGSNA01SXCH02.ana.firstamdata.com>
References: <F1B0F9305B343E43A1C3EECE48B853D509311C@CITGSNA01SXCH02.ana.firstamdata.com>
>>>>> "Husam" == Tomeh, Husam <htomeh@firstam.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@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, htomeh@firstam.com
Subject: Re: Optimizing a query
In-Reply-To: <m3psanjuey.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