X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 61AEF5337F for ; Fri, 22 Apr 2005 15:39:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24524-02 for ; Fri, 22 Apr 2005 18:39:21 +0000 (GMT) Received: from fep31-app.kolumbus.fi (fep31-0.kolumbus.fi [193.229.0.35]) by svr1.postgresql.org (Postfix) with ESMTP id 1DB4853367 for ; Fri, 22 Apr 2005 15:39:18 -0300 (ADT) Received: from [192.168.1.103] (really [84.231.185.8]) by fep31-app.kolumbus.fi with ESMTP id <20050422183918.XJHJ7490.fep31-app.kolumbus.fi@[192.168.1.103]> for ; Fri, 22 Apr 2005 21:39:18 +0300 Message-ID: <426944D0.6040706@kolumbus.fi> Date: Fri, 22 Apr 2005 21:39:12 +0300 From: Marko Ristola User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.6) Gecko/20050324 Debian/1.7.6-1 X-Accept-Language: en MIME-Version: 1.0 To: pgsql-perform Subject: Re: Bad n_distinct estimation; hacks suggested? References: <200504191209.05181.josh@agliodbs.com> <25382.1113943489@sss.pgh.pa.us> <200504191433.58960.josh@agliodbs.com> <200504201059.02033.josh@agliodbs.com> In-Reply-To: <200504201059.02033.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.34 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/621 X-Sequence-Number: 12018 Hi. Sometimes, if the random number generator, that PostgreSQL uses, isn't good enough, the randomly selected pages for the statistics might not be random enough. Solaris is unknown to me. Maybe the used random number generator there isn't good enough? Good statistics depend on good random numbers. So, for example, if you have one million pages, but the upper bound for the random numbers is one hundred thousand pages, the statistics might get tuned. Or some random number generator has for example only 32000 different values. Regards, Marko Ristola Josh Berkus wrote: >Tom, > >Any thoughts? This is really messing up query execution all across the >database ... > >--Josh > > > >>Here is the stats = 100 version. Notice that n_distinct has gone down. >> >> schemaname | tablename | attname | null_frac | avg_width | >>n_distinct | most_common_vals >> >>| most_common_freqs >>| histogram_bounds | >> >>correlation >> >> > > > >>-------------------+------------- public | web_site_activity_fa | >>session_id | 0 | 8 | 96107 | >>{4393922,6049228,6026260,4394034,60341,4393810,2562999,2573850,3006299,4705 >>488,2561499,4705258,3007378,4705490,60327,60352,2560950,2567640,2569852,3006 >>604,4394329,2570739,2406633,2407292,3006356,4393603,4394121,6449083,2565815, >>4387881,2406770,2407081,2564340,3007328,2406578,2407295,2562813,2567603,4387 >>835,71014,2566253,2566900,6103079,2289424,2407597,2567627,2568333,3457448,23 >>450,23670,60743,70739,2406818,2406852,2407511,2562816,3007446,6306095,60506, >>71902,591543,1169136,1447077,2285047,2406830,2573964,6222758,61393,70955,709 >>86,71207,71530,262368,2289213,2406899,2567361,2775952,3006824,4387864,623982 >>5,6244853,6422152,1739,58600,179293,278473,488407,1896390,2286976,2407020,25 >>46720,2677019,2984333,3006133,3007497,3310286,3631413,3801909,4366116,438802 >>5} >> >>{0.00166667,0.00146667,0.0013,0.0011,0.000933333,0.0009,0.0008,0.0008,0.000 >>733333,0.000733333,0.0007,0.000633333,0.0006,0.0006,0.000566667,0.000566667, >>0.000566667,0.000566667,0.000566667,0.000566667,0.000566667,0.000533333,0.00 >>05,0.0005,0.0005,0.0005,0.0005,0.0005,0.000466667,0.000466667,0.000433333,0. >>000433333,0.000433333,0.000433333,0.0004,0.0004,0.0004,0.0004,0.0004,0.00036 >>6667,0.000366667,0.000366667,0.000366667,0.000333333,0.000333333,0.000333333 >>,0.000333333,0.000333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0. >>0003,0.0003,0.0003,0.000266667,0.000266667,0.000266667,0.000266667,0.0002666 >>67,0.000266667,0.000266667,0.000266667,0.000266667,0.000233333,0.000233333,0 >>.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000 >>233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.0002333 >>33,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0 >>002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002} >> >>{230,58907,88648,156764,216759,240405,264601,289047,312630,339947,364452,38 >>6486,409427,434075,455140,475759,500086,521530,544703,680376,981066,1313419, >>1712592,1860151,1882452,1905328,1927504,1948159,1970054,1990408,2014501,2038 >>573,2062786,2087163,2110129,2132196,2155657,2181058,2204976,2228575,2256229, >>2283897,2352453,2407153,2457716,2542081,2572119,2624133,2699592,2771254,2832 >>224,2908151,2951500,3005088,3032889,3137244,3158685,3179395,3203681,3261587, >>3304359,3325577,3566688,3621357,3645094,3718667,3740821,3762386,3783169,3804 >>593,3826503,3904589,3931012,3957675,4141934,4265118,4288568,4316898,4365625, >>4473965,4535752,4559700,4691802,4749478,5977208,6000272,6021416,6045939,6078 >>912,6111900,6145155,6176422,6206627,6238291,6271270,6303067,6334117,6365200, >>6395250,6424719,6888329} >> >>| 0.41744 >> >> > > >