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 D6C6D11F6C43 for ; Tue, 11 Apr 2006 18:13:22 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 7YOZ4DMx6pL4 for ; Tue, 11 Apr 2006 18:12:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from crestone.coronasolutions.com (crestone.coronasolutions.com [66.45.104.24]) by postgresql.org (Postfix) with ESMTP id D3CFC11F89F9 for ; Tue, 11 Apr 2006 17:59:27 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by crestone.coronasolutions.com (Postfix) with ESMTP id 70F71644441; Tue, 11 Apr 2006 14:59:26 -0600 (MDT) Received: from crestone.coronasolutions.com ([127.0.0.1]) by localhost (crestone.coronasolutions.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27417-02; Tue, 11 Apr 2006 14:59:23 -0600 (MDT) Received: from [192.168.1.100] (c-67-165-220-189.hsd1.co.comcast.net [67.165.220.189]) by crestone.coronasolutions.com (Postfix) with ESMTP id 75D8564443C; Tue, 11 Apr 2006 14:59:22 -0600 (MDT) Message-ID: <443C18AA.3000500@drivefaster.net> Date: Tue, 11 Apr 2006 14:59:22 -0600 From: Dan Harris User-Agent: Thunderbird 1.5 (Macintosh/20060122) MIME-Version: 1.0 To: Tom Lane Cc: PostgreSQL Performance Subject: Re: Encouraging multi-table join order References: <443AAC26.4070802@drivefaster.net> <1543.1144710756@sss.pgh.pa.us> <443AEF9B.3000909@drivefaster.net> <2406.1144717297@sss.pgh.pa.us> In-Reply-To: <2406.1144717297@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at drivefaster.net X-Archive-Number: 200604/242 X-Sequence-Number: 18326 Tom Lane wrote: > > So it's estimating 5775 cost units per probe into eventactivity, which > is pretty high --- it must think that a lot of rows will be retrieved by > the index (way more than the 20 or so it thinks will get past the filter > condition). > What does the pg_stats entry for eventactivity.incidentid > contain? select * from pg_stats where tablename = 'eventactivity' and attname='incidentid'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+---------------+------------+-----------+-----------+------------+-----------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+------------- public | eventactivity | incidentid | 0 | 14 | 8157 | {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} | {P022140319,P030471058,P033090308,P041961082,P042910689,P050311006,P051350254,P052261148,P053270945,P060240316,P061000287} | 0.241737 > It might be worth increasing the statistics target for that > column to try to get a better estimate. > 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. Thanks again, Dan