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 22F6854032 for ; Wed, 20 Apr 2005 14:54:06 -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 94410-05 for ; Wed, 20 Apr 2005 17:53:56 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 4E53653FE6 for ; Wed, 20 Apr 2005 14:53:54 -0300 (ADT) X-EthosMedia-Virus-Scanned: no infections found Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7258539; Wed, 20 Apr 2005 10:55:51 -0700 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Tom Lane Subject: Re: Bad n_distinct estimation; hacks suggested? Date: Wed, 20 Apr 2005 10:59:01 -0700 User-Agent: KMail/1.7.1 Cc: "pgsql-perform" References: <200504191209.05181.josh@agliodbs.com> <25382.1113943489@sss.pgh.pa.us> <200504191433.58960.josh@agliodbs.com> In-Reply-To: <200504191433.58960.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200504201059.02033.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.048 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/515 X-Sequence-Number: 11912 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 -- --Josh Josh Berkus Aglio Database Solutions San Francisco