Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kQvMT-0004OJ-HM for pgsql-admin@arkaria.postgresql.org; Fri, 09 Oct 2020 16:35:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kQvMS-0004es-FK for pgsql-admin@arkaria.postgresql.org; Fri, 09 Oct 2020 16:35:16 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kQvMR-0004cA-Ew for pgsql-admin@lists.postgresql.org; Fri, 09 Oct 2020 16:35:16 +0000 Received: from mail-bn8nam08olkn2059.outbound.protection.outlook.com ([40.92.47.59] helo=NAM04-BN8-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kQvMN-0000ui-DD for pgsql-admin@lists.postgresql.org; Fri, 09 Oct 2020 16:35:13 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=V7GMAwm1fw/YR9EeknUfFVs0vHWZLf2hzsTC3LWDe2fdDxg/IMO88zy/YIxnbbmTV8M1i0eYowt00DvTPncIwEyaVfcMtIAJb/e7q+LOHh0WUcjdqAWqwrvZLQNaqPZwVPvcdIitiPzhJKGQbN58oMKEqfcWPQSB+WfPa0xZ85PA2ujLXlMnwOb+QfTzx2WonOSMWNzL2Gfrq+S7R+1t8BPc4M13hKzIIGwdiDlbSN/oH/LtgkfmrhGvYF/wq7YlVNeNKyYEcPC0vsSNa1088CWc7GsQWp/7+SwWEF2QRnXg9ubk/wn3IlFA4oV7BpqMI4tGc1teI8Ks+/LJTh4FEg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=BPgWN0n/RtpKD5IdKjIr914z9JrQxDI5aFBNm6FGQtE=; b=IaVqguPEuKsS0fGd2poH+mJ+cycneO2aZ0ITK4+ckI9gtgC9eYpuEbWisD3h/xdFEQMUeHjC53BjNw/mCrL1nnIoQFCJI1rw7OOXAqnn2ttsfuwXbbuP75FkhGseOKeovBelvebOzl1dkHLJvVMTJNc4TAk4MugOgnEGDArGHCnATZ22+ZLKn21OVPVhuj8rRJXpHg2iUncjRtKdZDvhuhbsm7ScVoa7AKZO8dgcIh+bRcCt1Ny4ENELUYPSKDiSEP1ZdZE5kHASKKzWXqi6RUsbmd3eMJeDBJY/7VdMK1OpiyjIw3OhYvIyRoSbHzQC7zV3KOqk2KuyEYL5ij4R1w== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=BPgWN0n/RtpKD5IdKjIr914z9JrQxDI5aFBNm6FGQtE=; b=HNVmG4OhDqI/YMrMIbbeNUM1rciWYOnw0Ui7Q5FoxRUk0WEsj2DY11WRJy06P44bjVBhnhDpdHrlnJurv9/9dmh+wWlYXAMoXp22ZxehO0YK8xJ6fbuFRiYohMES4lH/t6DTDUDuVlufBNMZ1tL0/QxzOS4VWS3MgGxwGkOxuqclbuPlRbGiQZCpa23SrxwGmiHw5Jt6ORL6HSi4p1RTGPF6IFhKQVUg1lej0fOooFjjjuSexpEwqukxswq4eihQq77JxyhdhQE+9D+KLyg2ztQnipDjm20MXGl+1p7Ygd86Vb1mLY5JLe/6maUtkihCPFjc5aN8FEx2ZiHWKHT0lw== Received: from DM6NAM04FT043.eop-NAM04.prod.protection.outlook.com (2a01:111:e400:7ea3::40) by DM6NAM04HT239.eop-NAM04.prod.protection.outlook.com (2a01:111:e400:7ea3::457) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3412.25; Fri, 9 Oct 2020 16:35:09 +0000 Received: from BYAPR06MB4023.namprd06.prod.outlook.com (2a01:111:e400:7ea3::50) by DM6NAM04FT043.mail.protection.outlook.com (2a01:111:e400:7ea3::132) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3455.27 via Frontend Transport; Fri, 9 Oct 2020 16:35:09 +0000 X-IncomingTopHeaderMarker: OriginalChecksum:3F7B12FAAE9EE1EE3F0948BA09D6FA77BEFBF74E7B5C3C7B7766016965FE5EC9;UpperCasedChecksum:41A149D2C79DA317F7D1084EB435A4B98C373E95773B8A26C2FE2D10BC5FEB4B;SizeAsReceived:8631;Count:44 Received: from BYAPR06MB4023.namprd06.prod.outlook.com ([fe80::3990:c66d:70e8:2fe5]) by BYAPR06MB4023.namprd06.prod.outlook.com ([fe80::3990:c66d:70e8:2fe5%3]) with mapi id 15.20.3455.026; Fri, 9 Oct 2020 16:35:09 +0000 Date: Fri, 9 Oct 2020 10:35:03 -0600 From: Hotmail To: Pgsql-admin Message-ID: References: <8f314df0-0ee5-452c-8dc6-8627b29acb48@Spark> Subject: Is there a way to correct an inaccurate n_distinct value for extended statistics in PG11? X-Readdle-Message-ID: b87c78eb-d6cd-472f-bd4b-73342916addd@Spark Content-Type: multipart/alternative; boundary="5f80913c_7c83e458_16348" X-TMN: [YRjlSlj4gyoRGQ3I2HCidybgXFVpKFPKOaL2LoVs7DxiK67SL1GKOLnLRH5/K9Z8] X-ClientProxiedBy: BY3PR10CA0019.namprd10.prod.outlook.com (2603:10b6:a03:255::24) To BYAPR06MB4023.namprd06.prod.outlook.com (2603:10b6:a02:84::17) X-Microsoft-Original-Message-ID: MIME-Version: 1.0 X-MS-Exchange-MessageSentRepresentingType: 1 Received: from [2601:282::16ed:20a3:490d:100:0] (2601:282:0:16ed:d0f1:ea8b:74b1:a989) by BY3PR10CA0019.namprd10.prod.outlook.com (2603:10b6:a03:255::24) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3455.21 via Frontend Transport; Fri, 9 Oct 2020 16:35:09 +0000 X-MS-PublicTrafficType: Email X-IncomingHeaderCount: 44 X-EOPAttributedMessage: 0 X-MS-Office365-Filtering-Correlation-Id: fda0483e-2a59-42a4-af46-08d86c714976 X-MS-TrafficTypeDiagnostic: DM6NAM04HT239: X-Microsoft-Antispam: BCL:0; X-Microsoft-Antispam-Message-Info: wP/mM+mNOu/cDOBadoXfrzONbzUqjDPDOU7YyqVTEj+OGv6B+zGvT02OZrbm/P96k4oDCpWRvsjAyjFCJTtDCV+r7YFtdEerk2VnfrsftuTaGhJY19AAssK8dY09BewtNCzMZgDm3KfUtNMeKkBNy4vr1vBkt+Sg8gAz1ZfQ8kp9w5Lt695DVEykG88cizpp6c/KaKcsyu4DjjC2p7NTWg== X-MS-Exchange-AntiSpam-MessageData: A6LtDjsK7Dr1G9RxusUkxS58zMMm2f4869fyNX4cOZKajvaqOgCSkmTUFwgfFa9VPYqBKFm/Ectjq0jWEICQ0/C8bpQMxO2+HOkR+KWjkaCN3X9qrZhZ/unXjTOxvmzOubVLmsBWfMxVXJmyvM97fNYUlZuYeRQaGszLTalvEfD51j3CJoIy6nY5T5MXLGytPvc9JZL2V9XH8ZHBktaxLQ== X-OriginatorOrg: hotmail.com X-MS-Exchange-CrossTenant-Network-Message-Id: fda0483e-2a59-42a4-af46-08d86c714976 X-MS-Exchange-CrossTenant-OriginalArrivalTime: 09 Oct 2020 16:35:09.6771 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-AuthSource: DM6NAM04FT043.eop-NAM04.prod.protection.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Anonymous X-MS-Exchange-CrossTenant-FromEntityHeader: Internet X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DM6NAM04HT239 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --5f80913c_7c83e458_16348 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi All, We have created extended statistics between two columns on a large table.= When looking at pg=5Fstatistic=5Fext the value for n=5Fdistinct for the = combined columns is off of the actual number of distinct values=C2=A0=C2=A0= by approixmately 40%. This is leading to a poor choice of leading tables = in some of our joins. With single column statistics inaccurate n=5Fdistinct values can be corre= cted with =22alter table alter column set n=5Fdistinct =3D=22 Is there a way to fix incorrect n=5Fdistinct values for extended statisit= ics=3F Thanks, Craig Jackson --5f80913c_7c83e458_16348 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi All,

We have created extended statistics between two columns on a large table. W= hen looking at pg_statistic_ext the value for n_distinct for the combined c= olumns is off of the actual number of distinct values  by approix= mately 40%. This is leading to a poor choice of leading tables in some of o= ur joins. 

With single column statistics inaccurate n_distinct values can be corrected= with "alter table alter column set n_distinct =3D"

Is there a way to fix incorrect n_distinct values for extended statisitics?=  

Thanks,

Craig Jackson 
--5f80913c_7c83e458_16348--