Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 843649FA162 for ; Wed, 13 Dec 2006 18:36:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 48561-08 for ; Wed, 13 Dec 2006 18:36:00 -0400 (AST) X-Greylist: delayed 03:47:21.407574 by SQLgrey-1.7.4 Received: from ore.jhcloos.com (ore.jhcloos.com [64.240.156.239]) by postgresql.org (Postfix) with ESMTP id 389F29FA28E for ; Wed, 13 Dec 2006 18:36:09 -0400 (AST) Received: from lugabout.jhcloos.org (ppp295.tc-1.buf-ch.ny.localnet.com [69.48.14.41]) (using TLSv1 with cipher EDH-RSA-DES-CBC3-SHA (168/168 bits)) (Client CN "lugabout.jhcloos.org", Issuer "ca.jhcloos.com" (verified OK)) by ore.jhcloos.com (Postfix) with ESMTP id 8A2FB1C57D; Wed, 13 Dec 2006 16:36:05 -0600 (CST) Received: by lugabout.jhcloos.org (Postfix, from userid 500) id 239A72BDC02; Wed, 13 Dec 2006 22:35:49 +0000 (GMT) From: James Cloos To: "Tomeh\, Husam" Cc: pgsql-performance@postgresql.org Subject: Re: Optimizing a query In-Reply-To: (Husam Tomeh's message of "Wed\, 13 Dec 2006 11\:25\:00 -0800") References: Copyright: Copyright 2006 James Cloos X-Hashcash: 1:23:061213:htomeh@firstam.com::R43J4PA696QU7Hyf:0000000000000000000000000000000000000000000CVqO X-Hashcash: 1:23:061213:pgsql-performance@postgresql.org::DCfV6Y7sor2mEDa7:00000000000000000000000000000F04b Date: Wed, 13 Dec 2006 17:35:26 -0500 Message-ID: Lines: 36 User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/23.0.0 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/344 X-Sequence-Number: 22400 >>>>> "Husam" == Tomeh, Husam 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 OpenPGP: 1024D/ED7DAEA6