Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B1E8E9FB262 for ; Mon, 5 Mar 2007 21:55:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 42029-01 for ; Mon, 5 Mar 2007 21:54:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C825B9FB232 for ; Mon, 5 Mar 2007 21:54:56 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l261sq1d025082; Mon, 5 Mar 2007 20:54:52 -0500 (EST) To: Jeff Cole cc: pgsql-performance@postgresql.org Subject: Re: In-reply-to: <9BAE31C9-93A6-48AE-AFED-3BB1947D13F1@gmail.com> References: <9BAE31C9-93A6-48AE-AFED-3BB1947D13F1@gmail.com> Comments: In-reply-to Jeff Cole message dated "Mon, 05 Mar 2007 17:05:41 -0500" Date: Mon, 05 Mar 2007 20:54:52 -0500 Message-ID: <25081.1173146092@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.06 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200703/113 X-Sequence-Number: 23518 Jeff Cole writes: > Hi, I'm new to tuning PostgreSQL and I have a query that gets slower > after I run a vacuum analyze. I believe it uses a Hash Join before > the analyze and a Nested Loop IN Join after. It seems the Nested > Loop IN Join estimates the correct number of rows, but underestimates > the amount of time required. I am curious why the vacuum analyze > makes it slower and if that gives any clues as too which parameter I > should be tuning. Hm, the cost for the upper nestloop is way less than you would expect given that the HASH IN join is going to have to be repeated 100+ times. I think this must be due to a very low "join_in_selectivity" estimate but I'm not sure why you are getting that, especially seeing that the rowcount estimates aren't far off. Can you show us the pg_stats rows for symptoms.id and symptom_reports.symptom_id? regards, tom lane