Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBTNnaL43229 for ; Fri, 29 Dec 2000 18:49:36 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eBTNnVC07781; Fri, 29 Dec 2000 18:49:31 -0500 (EST) To: "Joe Conway" cc: "PostgreSQL-SQL" Subject: Re: Optimization recommendations request In-reply-to: <018601c06d02$d007ace0$0705a8c0@jecw2k1> References: <018601c06d02$d007ace0$0705a8c0@jecw2k1> Comments: In-reply-to "Joe Conway" message dated "Sat, 23 Dec 2000 09:07:20 -0800" Date: Fri, 29 Dec 2000 18:49:31 -0500 Message-ID: <7778.978133771@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200012/295 X-Sequence-Number: 632 "Joe Conway" writes: > create index foo_idx1 on foo using HASH (guid); > SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; > The query currently takes in excess of 40 seconds. I would appreciate any > suggestions for optimizing to bring this down substantially. Why are you using a hash index? btree is a lot better for every conceivable purpose. It would also be a good idea to check to make sure that the query is using the index (see EXPLAIN). You didn't mention whether you'd done a VACUUM ANALYZE, so there's a risk the planner will make the wrong choice. regards, tom lane