Received: from femail5.sdc1.sfba.home.com (femail5.sdc1.sfba.home.com [24.0.95.85]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBTNdrL41612 for ; Fri, 29 Dec 2000 18:39:53 -0500 (EST) (envelope-from jconway2@home.com) Received: from jecw2k1 ([24.0.42.111]) by femail5.sdc1.sfba.home.com (InterMail vM.4.01.03.00 201-229-121) with SMTP id <20001229233949.ZLPK29808.femail5.sdc1.sfba.home.com@jecw2k1>; Fri, 29 Dec 2000 15:39:49 -0800 Message-ID: <00f701c071f0$9a9464c0$0705a8c0@jecw2k1> From: "Joe Conway" To: "Stephan Szabo" Cc: "PostgreSQL-SQL" References: Subject: Re: Optimization recommendations request Date: Fri, 29 Dec 2000 15:39:35 -0800 Organization: joe@conway-family.com MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200012/294 X-Sequence-Number: 631 Subject: Re: [SQL] Optimization recommendations request > > What does explain show for your query? > I sent this a week ago using the wrong (i.e. not the one I signed up with) reply-to address, so it didn't make it to the list until just now. In the meantime I ran explain and noticed that the index wasn't being used. So I ran vacuum analyze and now I'm getting the expected performance (and explain shows the index being used). If I understand the logged statistics correctly, I'm getting results returned in anywhere from about 3 to 45 ms, depending on cache hit rate. I also repeated my test with 15 million records with similar results. Not bad at all! I am still interested in any generic optimization tips for very large tables. Thanks for taking the time to reply! Joe