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 35111533FB for ; Tue, 19 Apr 2005 18:29:07 -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 24165-05 for ; Tue, 19 Apr 2005 21:28:57 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 65D8D53374 for ; Tue, 19 Apr 2005 18:28:54 -0300 (ADT) X-EthosMedia-Virus-Scanned: no infections found Received: from [67.103.46.98] (account josh@agliodbs.com HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7254757; Tue, 19 Apr 2005 14:30:52 -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: Tue, 19 Apr 2005 14:33:58 -0700 User-Agent: KMail/1.7.1 Cc: "pgsql-perform" References: <200504191209.05181.josh@agliodbs.com> <25382.1113943489@sss.pgh.pa.us> In-Reply-To: <25382.1113943489@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200504191433.58960.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/468 X-Sequence-Number: 11865 Tom, > What's the histogram itself look like? (I'd like to see the whole > pg_stats row not just part of it ...) There's probably no point in > showing the target=3D1000 version, but maybe target=3D100 would be > informative. Here is the stats =3D 100 version. Notice that n_distinct has gone down. schemaname | tablename | attname | null_frac | avg_width |=20 n_distinct | most_common_vals = =20 | most_common_freqs = =20 | histogram_bounds |=20 correlation =2D-----------+----------------------+------------+-----------+-----------+= =2D-----------+------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D---------------------------------------------+--------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D---------------------------------------------------------------+--------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D----------------------------------------------+------------- public | web_site_activity_fa | session_id | 0 | 8 | = =20 96107 |=20 {4393922,6049228,6026260,4394034,60341,4393810,2562999,2573850,3006299,4705= 488,2561499,4705258,3007378,4705490,60327,60352,2560950,2567640,2569852,300= 6604,4394329,2570739,2406633,2407292,3006356,4393603,4394121,6449083,256581= 5,4387881,2406770,2407081,2564340,3007328,2406578,2407295,2562813,2567603,4= 387835,71014,2566253,2566900,6103079,2289424,2407597,2567627,2568333,345744= 8,23450,23670,60743,70739,2406818,2406852,2407511,2562816,3007446,6306095,6= 0506,71902,591543,1169136,1447077,2285047,2406830,2573964,6222758,61393,709= 55,70986,71207,71530,262368,2289213,2406899,2567361,2775952,3006824,4387864= ,6239825,6244853,6422152,1739,58600,179293,278473,488407,1896390,2286976,24= 07020,2546720,2677019,2984333,3006133,3007497,3310286,3631413,3801909,43661= 16,4388025}=20 |=20 {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.= 0005,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.0= 00366667,0.000366667,0.000366667,0.000366667,0.000333333,0.000333333,0.0003= 33333,0.000333333,0.000333333,0.0003,0.0003,0.0003,0.0003,0.0003,0.0003,0.0= 003,0.0003,0.0003,0.0003,0.000266667,0.000266667,0.000266667,0.000266667,0.= 000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000233333,0.000= 233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233= 333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333= ,0.000233333,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002= ,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002}=20 |=20 {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,20= 38573,2062786,2087163,2110129,2132196,2155657,2181058,2204976,2228575,22562= 29,2283897,2352453,2407153,2457716,2542081,2572119,2624133,2699592,2771254,= 2832224,2908151,2951500,3005088,3032889,3137244,3158685,3179395,3203681,326= 1587,3304359,3325577,3566688,3621357,3645094,3718667,3740821,3762386,378316= 9,3804593,3826503,3904589,3931012,3957675,4141934,4265118,4288568,4316898,4= 365625,4473965,4535752,4559700,4691802,4749478,5977208,6000272,6021416,6045= 939,6078912,6111900,6145155,6176422,6206627,6238291,6271270,6303067,6334117= ,6365200,6395250,6424719,6888329}=20 | 0.41744 =2D-=20 =2D-Josh Josh Berkus Aglio Database Solutions San Francisco