Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCnJe-0089qt-TM for pgsql-general@arkaria.postgresql.org; Thu, 30 May 2024 21:28:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sCnJd-006C7F-7w for pgsql-general@arkaria.postgresql.org; Thu, 30 May 2024 21:28:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCnJc-006C3A-DS for pgsql-general@lists.postgresql.org; Thu, 30 May 2024 21:28:04 +0000 Received: from mail-dm6nam12on20601.outbound.protection.outlook.com ([2a01:111:f403:2417::601] helo=NAM12-DM6-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCnJY-002ixo-EK for pgsql-general@postgresql.org; Thu, 30 May 2024 21:28:02 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=oBkGXuz/HiYJuCD+PVDmGGGhJMuYNZdb5A15F74HgHSRe7CWDMAsq3toL5gqYWfLA1pTxWIjHQ3lbAK7guS8G+5LPd6x8X2X3NA5FTkWEF+GIEDMH0Qq8o2WTQSFDWeflVv+wzDS4xG7JBPuVrJHov8hka0r7W3o7NDsaBJXPswCtGti6FQTimQUhnImuL6SlkPONjbfyIVYvLNs6nuB2anaJUhQYLvabIkR51nhAuNI+RDUxatTOyG0Z7R3FT0ZfYYFYHU78v/XORZ0tY8iYM+yekzLf/XuEkCjBNYYQqkZ22EoPKeskKgwme78/3Vop138fgDyjvSoyM9nzDABPg== 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-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=dGDfSnWwyBK9NOYGvM+BGaPz7TVrv9OALoQXF+ghGKQ=; b=RHbIRPpRIQLJvrV5kh1nVlmhY95cuiHGc+ElG5H4obf11y62tR59wJslW8C2aG/uo5qKzdW7OOWqJPG9303rCzkrl7Q9FcQHUxrbWTpTAX/zK1lmAAUnpVhkKTyILsFnXUUdc/X1lJMR8tcHyci78QMyjXPGiLd6YQdVsZE15qfbWSeIA/nmJZAr6+Y9278qXwZ6yppNuJ2oTUHttxUx5bL11HidIjL9EAkp2u6tmOlBHX0XqHzT/jh20VR1DYEGzhyH+/3ZYcmnRNnsFvpDMNHmxJzwktGvHVD72zZ7k0JkHUtoafoql8QCyIjDbr/rQCn15+4wqp5oPlYECrHNUA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=thomsonreuters.com; dmarc=pass action=none header.from=thomsonreuters.com; dkim=pass header.d=thomsonreuters.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=thomsonreuters.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=dGDfSnWwyBK9NOYGvM+BGaPz7TVrv9OALoQXF+ghGKQ=; b=Ybw2nwz9Q67ql7aKPNCsdo6mHVuZXaZ5ll7MnxPbM5z5F/DsRrwbkiP5RuqQFFeO423gZi+lpRZHIIGaulSOn4iHXisUlR9y9fEMY8SzOCZGmTl5keZx92bQCcufXiJMzjj5HPnLwMRxD6cBTzf4umFiE1kWOHdYAqEC/Jt9HTk= Received: from CH0PR03MB6100.namprd03.prod.outlook.com (2603:10b6:610:bb::15) by SA0PR03MB5596.namprd03.prod.outlook.com (2603:10b6:806:b8::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7633.21; Thu, 30 May 2024 21:27:57 +0000 Received: from CH0PR03MB6100.namprd03.prod.outlook.com ([fe80::db8:9703:284a:ef55]) by CH0PR03MB6100.namprd03.prod.outlook.com ([fe80::db8:9703:284a:ef55%5]) with mapi id 15.20.7587.030; Thu, 30 May 2024 21:27:56 +0000 From: "Wong, Kam Fook (TR Technology)" To: "pgsql-general@postgresql.org" Subject: How to delete column level Stats/Histogram Thread-Topic: How to delete column level Stats/Histogram Thread-Index: Adqy1xJv8JoJiF9QQp2dVtXjfrLgSg== Date: Thu, 30 May 2024 21:27:56 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=thomsonreuters.com; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: CH0PR03MB6100:EE_|SA0PR03MB5596:EE_ x-ms-office365-filtering-correlation-id: 869e41f8-fee2-4652-be9a-08dc80ef5f9b x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230031|376005|366007|1800799015|38070700009; x-microsoft-antispam-message-info: =?us-ascii?Q?eTtxw3nD1MKE6+tJeTjrCJJNegDJIWv0MHGKbsDgNbhFvBdWU5TjhkaEp1B1?= =?us-ascii?Q?yMWcx5BOoc9y3JCcoV1l7x2AxQ4RXIY6ubdGydT8b3qhEU+vm9NH6/DbA99k?= =?us-ascii?Q?VMpR+a1omdmLAxoL/dkNontN1oi33gH2EfghB538J2iHjm2Dp9JM5byJImNI?= =?us-ascii?Q?27HrLILaZk9kXI8XfA2rbu5HsuZEG9tLOxl2qLYrG9kH/14NakQZ3fg7ZG4h?= =?us-ascii?Q?UqGbrIPPmBM/iFFjN0kspOD5SzgITF/4/ztWuBmjEVaOeqmZZEAmZj3nmNop?= =?us-ascii?Q?P6qflR4nk93pHeksnSrQFDVZZyyTumrtVka1ASGDH5voV3u2+IG895R1+5yn?= =?us-ascii?Q?TGe3ENndnTOI5YnJ5PalFcLP95RZwQLjQfDgD3umjMze80bbQXIFz3jTFL0v?= =?us-ascii?Q?833Ya0ZTZpSf2M8IkfFS+WCuH1RPCYEFfP5mTAmIo94t5qdaElaoKK4IA3r6?= =?us-ascii?Q?4dJkomCrUy0wd8Y17e99MEdxKuq1LTtpiGjfX/BKcVTjHAhz0TMHQfQ8ySO5?= =?us-ascii?Q?77mFc+KCwXqrLU4hbOIeSrUdGBwbonUi8P8LTgckSdDphREdswu1vRrC/14X?= =?us-ascii?Q?b0SSH7L5M706Shi7RqfyNQXCLyE3GkZ0BzDwBlHxqWu5BInDeWbCR+OHZ0q+?= =?us-ascii?Q?+5xy7iB0guAxqjnWclVTC0FZQ1auoi43P04IxKrZlRRMAdZS1/GKD61Id/aT?= =?us-ascii?Q?ZBrcbaOBE+Xnc6c3EW+3YN/5qzefOWkOjJ2VbmjH+VIfKXGWEBu7eYuud+wu?= =?us-ascii?Q?EbsuikBTWpkWp8nRPzPg5r3COcLQEGS99dE0u5CzbRfq7f3J3iBiWkexmVgG?= =?us-ascii?Q?bdYvotQZADdENO06TCkmFKcQAeHLnz1/A/aQsH/gflTG2jyHBABvxCnbTkKC?= =?us-ascii?Q?qJELLvbQ3Cv5h5RTvnXf6hs41TvI1qpvl5Zbse8uCvzpe2czNoVcPtkNNPZy?= =?us-ascii?Q?/+Cl1UALUpsJsMSVXuF1GU+ITR0n/whzL4WddLwzBi4uVeDl6P7iTqvo4HRe?= =?us-ascii?Q?0Ek/qDkzvPGjPMFajrYsQ8lSz3nDtkWBs/s4sXJcUm1ENQZ35bv9Kr7d2iCx?= =?us-ascii?Q?lNo2jvcw6OyQBzO0zOT9xEKKHC37pTUjix+DPrhh/Rhf0E7XYJWOLhx8f/3B?= =?us-ascii?Q?3UcnvjIysQp/5ceu6/Yy4wxoL1ZH9Iaqgeh28uDsYY1hkteE2f+dVAI2mtHb?= =?us-ascii?Q?8OPlu4fIMqP221zU3Coz2Ougb+fSYvxvUu7fXkpBxoVdCDrYrBozzNBVH98T?= =?us-ascii?Q?I5VbxNhSA2djDlG2EtbZZlsYqIjF7Ag7BU2COP1OjC7IvxFp6oqldB54ro+t?= =?us-ascii?Q?vq6whNfPyJ2j7caXWnDQRYs8hvk3/afdvfcPc19mXYH+LQ=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:CH0PR03MB6100.namprd03.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230031)(376005)(366007)(1800799015)(38070700009);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?gwWC5EXDZQoBHHXolYBOkMvp3jQDBuuPxtg1DE6OjZNgyknAV3KbKqcckaYd?= =?us-ascii?Q?9nRHepR/IU2TWV78j4RDF70fwvSlhgWl8Er4wXeTIfktCKLWNai0RvVjMC5A?= =?us-ascii?Q?gFXYF3i0rtVrOZvg0BXSRjjZ0YQoukxbCtMgOBr3Qp04XXYMbo+HlMhuVRnm?= =?us-ascii?Q?uVI65s/dk1wgdSOpMSAkxRRBvEimy00UMrqfIYooc/d/MmTpauTODt2vi+w5?= =?us-ascii?Q?uDkfHmVs8MRTfG2BEfNep2ArudHXPyjgwo/TeHLhNRqDYisZWQ9KHPMfqx+3?= =?us-ascii?Q?cWZ6A9n3ODBYP+aXCsXEuMW/zWtET+yyOX4fEvsGX5xU8og93bNkyPcLgQ/h?= =?us-ascii?Q?8VP+7NnIy4yEsR0Co/amtyqsNdFYWiRm2qHqDLMXdEXabAgYg9DYqH8oPtrf?= =?us-ascii?Q?N98NKOsmjjm6eEw/XHk+kLUGsT6FOwOQSt8cT+/R3LWnOKCqf1/rYzgA5WAt?= =?us-ascii?Q?O1CiYG97Z8m7yQ/VYKUUjkPzx3sG+S1BmH6a8FyQavXdxy/3jaQcmVOyq4D2?= =?us-ascii?Q?wd9pHkuNsPv2eceV0E84fi84vqGytVsovRZCwr0VCg/W3niUvtHi7uClyAzf?= =?us-ascii?Q?LF5i6UUWae0BwXjeOoYGg+FK9iyldXshPsOOgsmyeEpPfuhjPqjiWa3xA7ic?= =?us-ascii?Q?Tkx1QVCNLJedX/mY0DTYJAxQBk7UfStQ2J9od/e/JHDApKiF1DsbQUlncYOl?= =?us-ascii?Q?4qngIuDo/ITBpD98PXrPueQSo9gON9OVrxK1OCFZ6NfkYRmPDVAlqkAoN3Fz?= =?us-ascii?Q?0WfFTT6F7N0Xuumaw1usciyVJSCiXvxGQHnnxAgqR5YvzTDMuJBiI1+bEUia?= =?us-ascii?Q?q+iA6hVEan3LtMdxKMxTQSDopAQcsS3nhT+DYA/tXRhFvmYSPynkQgBtHy0e?= =?us-ascii?Q?/n0ZoXs8YiEsMJdLV/SWkuVM41inOTYfMbNJyDE8A0tld5NgnsBOFT6dtoU/?= =?us-ascii?Q?fJg7XtTpYEdNmKlbyEH8o95LdmqEafa3akkr897NRTvIoYneQ7wD/nIIQoiZ?= =?us-ascii?Q?ifALNPtJP411jsYTaRKffIq53yURQPDL7B7Zr/FRmWegDN/KJ/Cful2770c8?= =?us-ascii?Q?mIXw8/KWTrBqrkodAyCZuxI/9AmRFJeEU1Q6RzfjPCikuyHKXGQ3WoLwqwtd?= =?us-ascii?Q?5oo6vo1dckmdhsPfVXX0eLs+E9F3zSEq9FMhZVUCX2W4o38GV7eeXcqtQ1Hf?= =?us-ascii?Q?Slp4krkVB2jn2O/RwuTmNHuGaK/hLxYV7NHElHMhY3HdbfRTQp2Z5WDJjygn?= =?us-ascii?Q?ZSYzil6D25k9udEUDEvXVO8i0Gj0WardMOqQc4lfwmVswaHRo5RWPp3WhX7H?= =?us-ascii?Q?lLmvuiDkc6yfOw1USI2lMTjF0Kojz9tNJ/P2fA5yYCI/Y4d7P24Ah5/gY4wL?= =?us-ascii?Q?PIVvbN+Gu0qnQW1EsAe/o+CQ7yqOxH1Wvh23Pww3645+Q3y3LOEyzQUOjLji?= =?us-ascii?Q?3gH8g2qEe9++Wa/nXw+MznRJ0RQQzXz0TTHZIMCzmW6aOaoS06JjmY2S83dL?= =?us-ascii?Q?cN2WauUkcJlzEFWwyXUVjGD6zwUCy/W7iczCaf4/dtmBD5JdEFpEx84eQyps?= =?us-ascii?Q?/poWsb/KBcKGTWKAZe0B6o96QZGfV6qJJRW3ZH3V29l2YRlWwdHgDp+PwhUe?= =?us-ascii?Q?hQ=3D=3D?= Content-Type: multipart/alternative; boundary="_000_CH0PR03MB61008E885DA648855B38E273FEF32CH0PR03MB6100namp_" MIME-Version: 1.0 X-OriginatorOrg: thomsonreuters.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: CH0PR03MB6100.namprd03.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 869e41f8-fee2-4652-be9a-08dc80ef5f9b X-MS-Exchange-CrossTenant-originalarrivaltime: 30 May 2024 21:27:56.8977 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: boaOks45H73Q15DPpY7uxZCc0FzVGEMi0P73z3xCcNZtMge8dY72sI1lfuVykOP0MC9stRJ6hqjX6ccZJ5mR5VCtRYbMPYzso8TyTsj4WXA= X-MS-Exchange-Transport-CrossTenantHeadersStamped: SA0PR03MB5596 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_CH0PR03MB61008E885DA648855B38E273FEF32CH0PR03MB6100namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 1. Is there a way to delete a specific column level stats/histogram. Th= e following approach does not work. alter table abc alter column bg_org_partner set statistics 0; analyze abc; select * FROM pg_stats where tablename in ('abc' ) and attname =3D 'bg_org_partner'; the most_common_vals remain and most_common_freqs remain the same. 1. Any other ways or workaround such as wiping out a specific table leve= l stat, then restore the stats but minus 1 column. And the future vacuum a= nalyze/analyze will not be updating that specific column stat. Thank you This e-mail is for the sole use of the intended recipient and contains info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be = accessed on our website: https://www.thomsonreuters.com/en/resources/disclo= sures.html --_000_CH0PR03MB61008E885DA648855B38E273FEF32CH0PR03MB6100namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
  1. Is there a way to delete a specific column level stats/histogram.&nb= sp; The following approach does not work.

 

alter table ab= c alter column bg_org_partner set statistics 0;

analyze &= nbsp; abc;

 

select *

FROM pg_stats where tablename in ('abc' ) an= d attname =3D 'bg_org_partner';

 

the most_comm= on_vals remain and most_common_freqs remain the same.

 <= /p>

  1. Any other ways or workaround such as = wiping out a specific table level stat, then restore the stats but minus 1 column.  And the future vacuum analyze/analyze = will not be updating that specific column stat. 

 

Thank you

This e-mail is for the sole use of the intended recipient and contains info= rmation that may be privileged and/or confidential. If you are not an inten= ded recipient, please notify the sender by return e-mail and delete this e-= mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://= www.thomsonreuters.com/en/resources/disclosures.html --_000_CH0PR03MB61008E885DA648855B38E273FEF32CH0PR03MB6100namp_--