Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F0FA29FB229 for ; Tue, 6 Mar 2007 12:41:12 -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 47444-03 for ; Tue, 6 Mar 2007 12:40:55 -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 2BF6B9FA110 for ; Tue, 6 Mar 2007 12:40:55 -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 l26GeroM005645; Tue, 6 Mar 2007 11:40:53 -0500 (EST) To: Jeff Cole cc: pgsql-performance@postgresql.org Subject: Re: In-reply-to: <43756010-6EF6-4F91-B84E-E7D29599C3E8@gmail.com> References: <9BAE31C9-93A6-48AE-AFED-3BB1947D13F1@gmail.com> <25081.1173146092@sss.pgh.pa.us> <43756010-6EF6-4F91-B84E-E7D29599C3E8@gmail.com> Comments: In-reply-to Jeff Cole message dated "Tue, 06 Mar 2007 10:40:11 -0500" Date: Tue, 06 Mar 2007 11:40:53 -0500 Message-ID: <5644.1173199253@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/135 X-Sequence-Number: 23540 Jeff Cole writes: > Hi Tom, thanks for the response. Here are the pg_stats. I think I > understand what the stats say, but I don't know what to conclude from > them. OK, the symptom_id row claims there are only 80 distinct values of symptom_id in symptom_reports. This is a bit low (looks like the true state of affairs is that all but 2 of the 108 entries of symptoms are represented in symptom_reports), but it's not horridly off considering that you're using the rather low default statistics_target. What happens is that the planner expects that on average only 80 rows of the inner join will need to be scanned to find a match for a given symptoms.id, and this makes the nestloop look cheap. However, per your previous EXPLAIN ANALYZE: > -> Nested Loop IN Join (cost=149.05..586.26 rows=85 width=0) (actual time=54.517..3441.115 rows=106 loops=1) > Join Filter: ("outer".id = "inner".symptom_id) > -> Seq Scan on symptoms (cost=0.00..3.08 rows=108 width=4) (actual time=0.007..0.273 rows=108 loops=1) > -> Hash IN Join (cost=149.05..603.90 rows=13074 width=4) (actual time=0.078..24.503 rows=3773 loops=108) the *actual* average number of rows scanned is 3773. I'm not sure why this should be --- is it possible that the distribution of keys in symptom_reports is wildly uneven? This could happen if all of the physically earlier rows in symptom_reports contain the same small set of symptom_ids, but the stats don't seem to indicate such a skew. regards, tom lane