X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C4B7A11F7DD4 for ; Tue, 11 Apr 2006 18:20:54 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.127 X-Spam-Level: X-Spam-Status: No, score=0.127 required=5 tests=[AWL=0.127] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id dBAzRY+xa42I for ; Tue, 11 Apr 2006 18:19:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id AE86911F68FE for ; Tue, 11 Apr 2006 18:08:54 -0300 (ADT) 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 k3BL8r8E010771; Tue, 11 Apr 2006 17:08:53 -0400 (EDT) To: Dan Harris cc: PostgreSQL Performance Subject: Re: Encouraging multi-table join order In-reply-to: <443C18AA.3000500@drivefaster.net> References: <443AAC26.4070802@drivefaster.net> <1543.1144710756@sss.pgh.pa.us> <443AEF9B.3000909@drivefaster.net> <2406.1144717297@sss.pgh.pa.us> <443C18AA.3000500@drivefaster.net> Comments: In-reply-to Dan Harris message dated "Tue, 11 Apr 2006 14:59:22 -0600" Date: Tue, 11 Apr 2006 17:08:53 -0400 Message-ID: <10770.1144789733@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/244 X-Sequence-Number: 18328 Dan Harris writes: > Tom Lane wrote: >> What does the pg_stats entry for eventactivity.incidentid >> contain? > {P043190299,P051560740,P052581036,P052830218,P053100679,P053190889,P060370845,P042070391,P042690319,P043290117} > | > {0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333} > How high should I set this? I read the default is 10, but I'm not sure > if doubling this would make a difference or if I should be doing a much > larger number. There's approx 45 million rows in the table, if that matters. What the stats entry is saying is that the most common entries occur about 75000 times apiece (0.00166667 * 45e6), which is what's scaring the planner here ;-). I think those frequencies are artificially high though. The default statistics sample size is 3000 rows (300 * statistics target, actually), so those numbers correspond to 5 or 4 rows in the sample, which is probably just random chance. Try increasing the stats targets for this table to 100, then re-ANALYZE and see what you get. The most_common_freqs entries might drop as much as a factor of 10. regards, tom lane