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 1shWXn-003LYb-TI for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2024 15:49:44 +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 1shWXk-00EcSm-TU for pgsql-admin@arkaria.postgresql.org; Fri, 23 Aug 2024 15:49:41 +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 1shWXk-00EcS2-6x for pgsql-admin@lists.postgresql.org; Fri, 23 Aug 2024 15:49:41 +0000 Received: from mx0b-000e5c18.pphosted.com ([205.220.181.92]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1shWXc-0015DR-Mr for pgsql-admin@lists.postgresql.org; Fri, 23 Aug 2024 15:49:38 +0000 Received: from pps.filterd (m0285255.ppops.net [127.0.0.1]) by mx0a-000e5c18.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 47NCg8gb029079; Fri, 23 Aug 2024 15:49:29 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mdlive.com; h= content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=pod0510241234; bh=x5y+aCeqY7KrmN9gGRiL 8DikqlTtxUzqU1Loynl6Obw=; b=PJYhX/yr9xuByZfUXiTwv14So8HASG2ItRJ8 6md57JwDXTg73vw4BhJRYvSSfGbWJJRDvwMhFwxzqeC3YdmczAczS8ZGl+Ftjnzo rFourVg9JVOVoU6vCZD6SSIQ8gcjTnQeU9B0Ho+e97TPn8WkVmqJ1+yDkpBZCBNb D1AZ3qtN7vTXnz4w02TwssRW869JNS8Ls0SW6dFAHHVscjFqonxHX2DGNUTCD6Vh GHrsKDBjn5UdsUH9+ZNvsS4nrc9EcSWIHcw5nJIyt8jeRnF1saLB2YKvhPECne/J JSZrWcNVKvZ7ZLIidlHwyvT/M6cgUDGMx/8o7y0p87VgqgNl9g== Received: from eiaappxp0011.gid.prd.globalcore.com (lyncav03.cigna.com [170.48.19.150] (may be forged)) by mx0a-000e5c18.pphosted.com (PPS) with ESMTPS id 415nbfumw1-9 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Fri, 23 Aug 2024 15:49:29 +0000 (GMT) Received: from cvlappxp20768.internal.cigna.com (HELO CIWX9MAP0058.internal.cigna.com) ([10.31.88.209]) by EIAAPPXP0011.gid.prd.globalcore.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 23 Aug 2024 15:49:28 +0000 Received: from CIWX9MAP0076.internal.cigna.com (10.25.171.148) by CIWX9MAP0058.internal.cigna.com (10.25.171.126) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1544.11; Fri, 23 Aug 2024 11:49:28 -0400 Received: from NAM12-BN8-obe.outbound.protection.outlook.com (10.120.223.5) by CIWX9MAP0076.internal.cigna.com (10.25.171.148) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1544.11 via Frontend Transport; Fri, 23 Aug 2024 11:49:28 -0400 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=K5C5W1VTB0bCDuePNQioK2/t7L0Xn1u+XbuwSm/FpDnLvLwDgEe3s9ZcUfPa0vfrz9mXVPfU1B5NSBO97kbKSRv/oJIZeWET4L67mMIhdR32aGjGaEJ1iFwFPjwaTfX+uXb5cKpNxp6BFWU48oa+s2R7p38JYT799wmMTo289ojNXoQ0viPm1J19zNW2jMktGJ4MZ+gTZ7qZdnzNickM5s6YtYbHvSR2r09B8SYZw0oxYoom0VMcDyjSBruwQQXVSIegXFTIUe1kA9ScDu2Ky2TQQ/kIywVy7UaAdMlPCh1egs4a4vD0xIja79M5VINzlUh3ztEGAjUrYrwpfXpHcQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; 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=lowWAZJtH4aYH+eMr0k8mHpvuLdlGOJiEwl3zqMCrgI=; b=aCzeGB9JSUbt7lUyUeUKcXJYNDVIAuL3PW6ctXxBUmojLGH//1SDeLOglDosBwEAf5UB7Tf1/EciTui7UroEBKwg6KcjFLgwXsX1AJUg1mA7b/OpDLtKwFhR/2bkPDBmw5iGmmgHD5YJfwJZAzfGHCtgpHhsORLCuUnRzCxaw2zv/Y2ULvXkmheC5l91e5cmRQ0eKwmudYCNAEio3/L86umzCZTM2YSgoWSGH19PRdpWNdcVNO/5QXOLr+IO7ENDQNnWYusE/5Qhb9L3ILlotp/XMsvYZG0Jh4WvEuw7/cTWY2CGuH3jRWBNO9f5046odpifAXxQTqpc9nrxCZcpKA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=mdlive.com; dmarc=pass action=none header.from=mdlive.com; dkim=pass header.d=mdlive.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cignatlp.onmicrosoft.com; s=selector2-cignatlp-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=lowWAZJtH4aYH+eMr0k8mHpvuLdlGOJiEwl3zqMCrgI=; b=qtc0lHe2xOT2bhmXAMu8LYxdhhSjo2v+rS+/WmfTMhB+EG6Q8j70K4oxksD7f8GlpYPwr6dIhSl8rfWwXz/Mu3wY9nmYjGOoE8WqbJVIfyaH7vWaa4HFxVV6PyCMrpA2AtBRXNR+590NO7tpiwLr38hfBhCVCJJE82COxVAFd3Y= Received: from LV3PR12MB9332.namprd12.prod.outlook.com (2603:10b6:408:20f::14) by IA0PR12MB8863.namprd12.prod.outlook.com (2603:10b6:208:488::13) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7897.19; Fri, 23 Aug 2024 15:49:27 +0000 Received: from LV3PR12MB9332.namprd12.prod.outlook.com ([fe80::c67a:4999:bfb9:cf1b]) by LV3PR12MB9332.namprd12.prod.outlook.com ([fe80::c67a:4999:bfb9:cf1b%4]) with mapi id 15.20.7875.023; Fri, 23 Aug 2024 15:49:27 +0000 From: "Wetmore, Matthew (CTR)" To: Scott Ribe , Pgsql-admin Subject: Re: checking for a NULL date in a partitioned table kills performance Thread-Topic: checking for a NULL date in a partitioned table kills performance Thread-Index: AQHa9W+iRnMASht3hk2ihX6PQeY/5rI0h9cA Date: Fri, 23 Aug 2024 15:49:27 +0000 Message-ID: <17C7D941-73CB-41BF-BE8F-BB2788049F1F@glbcore.com> References: <010001917f08731e-0052ded1-1c0a-4f39-a63f-3e99767c5f59-000000@email.amazonses.com> <1F2BA008-3732-4289-876A-EF6889474E08@elevated-dev.com> In-Reply-To: <1F2BA008-3732-4289-876A-EF6889474E08@elevated-dev.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-publictraffictype: Email x-ms-traffictypediagnostic: LV3PR12MB9332:EE_|IA0PR12MB8863:EE_ x-ms-office365-filtering-correlation-id: cf72e424-8764-4fdd-047e-08dcc38b2b35 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|38070700018; x-microsoft-antispam-message-info: =?utf-8?B?a2V6Z2hyb3FoUVVKQkU4T0NIV2hsZTFidHo1dTNPcnhqa3RlUTBMMXgzU3Uy?= =?utf-8?B?VUU1cVJJSW8wdkJZVlcyS3JLME5ENUsxcElDZ01mdnMwN0RaNFE4VWNBaEJq?= =?utf-8?B?b1dSYnBjZDZaWURhRFdEMXBJRi9wR1pqaXcyMHlzbDBwUmo5UTg0QTJOQjlw?= =?utf-8?B?OGhGTzc0bUdWUHRJRmFXekw3WGxvWFNFTHY3WEhTSzVDQytvUGpVVjNPeFdm?= =?utf-8?B?Wll1NWZjWmZtbFdWeVFtdWhCZ2JyU0NVM0hab2JQN1lSZ2l4Z2dabFhob3dH?= =?utf-8?B?aEdDa3U3dHVLYklhbzlPYlpkNnpycW5wQlo4TUZwODdOeGhhZjV4eWpoYlJ1?= =?utf-8?B?bndtTXVqZ3ZkSnZsaWZFbG4rd3d3ZWJTZDREN1EvN1FzQTI2MTlxZFJ5eGRx?= =?utf-8?B?RHEyNmRHbmY4ME54bVB0MktXaDN6elFTZFRwdlB5emdWWTJTdVZacFp1dUZH?= =?utf-8?B?UUVySndneStSaEJVVzhMQWtISTE4amg1NldNeFFNZCtveDJBTWRqYks5bEhy?= =?utf-8?B?M0YrRS9iWnREY3Juc2JHaEppWmtoK2tjWmRLK05qalFlMHBOT2gzS25sbDNN?= =?utf-8?B?dkJWZ1JOODdJL2pOVGpTVTErZi9lMnlJS3NqaVNZdTE3YkxyRzRYdHJ4S3Jl?= =?utf-8?B?bGsvOWNvK0xOVHdSSXlqYlVhNkRuOFJQNGpZLzM0cUtQcFZjdndnTkUyVDNO?= =?utf-8?B?WnFpd0VqYVNPM0t1UEZ3WnFOeFpaZEJMTng1YkNWVDlkOWsyYjVqeHQ4QXNt?= =?utf-8?B?Q012RnFJUmcvMTgzRHdlRVpqcStRRWN5bjUwaFg2czd6WDk2bTZQM2h0djlj?= =?utf-8?B?cE1zRk5qL0VBS05IQldianZxRGwyc3hFNDZERnYzMmwyT1paREhEajl6cVc0?= =?utf-8?B?SmxUWjF0Ny9rRy9ZTW9iRjJod25IYm9MYWJGalN5b2FEeW1pWS9rNE44SVJF?= =?utf-8?B?LzFXU0Nyb2dlVTdHdmNVY2ZrR3R1NUxCK3JPeFZoYmwzY2tINlZncWhHNkJP?= =?utf-8?B?bkN0a1BjTnlaaUk4R05VQ0FlVThUSHorTy9yb01yUDY3TFYzR2UyUjRKeFd4?= =?utf-8?B?VUcyNXdQTm5tYXV6VW9JOUIydC90QXpTaUlNeWI0QVV0Zi9nNnBaUmpjaEVx?= =?utf-8?B?YmJSNFNwa3lhbkVVQmk0NzRMMlRFWlJ3dG5GUGhCWmFYRzRwVnMwMEJHUVVW?= =?utf-8?B?RnJ1T0tkV2ZtU3M1YnJ0V0NvK0xRR1VIRTU1LzQyNWs5djhmaWgyY1Z1SFgr?= =?utf-8?B?aUk5ZGlEOVVDV1FzeXhtYTVlK25QS1RHWjY3Z0UzVTA1bGtWNXNGblNOVHlS?= =?utf-8?B?aHhaZjRtUEZEM2MyZDhzMmxWUDJPTTNreUExcXpqV2Rkc1djNXcvUHliZWIz?= =?utf-8?B?V2hUbU5UNFRoVkZEb0h6ZUd4ajFJYzRlNm8yR1AvODY5QllOVFBVSFh6cjVv?= =?utf-8?B?ay9rTkVmTzU1UVdJYW1KOVVvZ2tvd1ViZHA5T1Q4Q25ua3d2Y2RlaUgrdFZo?= =?utf-8?B?bTZlMWttemtpVHBQVXBXVDBORlNmSklDOEdGMktFZFpsUUN5TFp6TWhqYmhr?= =?utf-8?B?VjZ3VXJvelAwdjg3dUVPcGNwczZSSnJoQmFBQytiSVBicU5zVEJxTGg3b0RT?= =?utf-8?B?bnFqbFU1bk1OYkZ5OHZST2Nwb3k3cU9ud1RUbk9RY2JiVWJxVDZJNzZzMGRN?= =?utf-8?B?QkViRUl0aEF3U2pRUVZZT0NtSkJvS0ovUFphTVFETytYeldGUXR2K2RMMXpt?= =?utf-8?B?N2lleTdFeUY0Wks4QlVUdzJxaVAvVDdxQXpGKzJjVmZLQzgvMThHb1dWcnJF?= =?utf-8?B?Tlg5dGllcXVYamZIQkxiNkVGRG5OUWh5WWlVRHZFSWRJcTRNN2ZOcEk2akp3?= =?utf-8?Q?av8ksHgy9a+Mu?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:LV3PR12MB9332.namprd12.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(376014)(1800799024)(366016)(38070700018);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?utf-8?B?V2dXYVpGVUhwanVtblpGUGEvNnNLS0dLNnE1UEhoWTllWUJYc1FnZXE3NlpC?= =?utf-8?B?MU9WejNkRkpwWHE5MnJuT2E1NXJPS1hYdFI4ZXdOdHczcHU5WlNOWkxHQ0NO?= =?utf-8?B?Wi84TWJkcUpGNTY4QitCKzRuaUtIUmtBZTNpSlJPbXQrQnF3bEt3c0pQVCtW?= =?utf-8?B?QU1GMVZoeldlVTBGTXQ3MHhlTW1HS3p6MDcyT0ZOMVlLOTRxMHNHUzVzZ2hu?= =?utf-8?B?M0dwTTVPZzl4L0tyUWN1L1FpSnFqQWRsaHdMa0FCK3FMTzFrMTh6SjFRRzRM?= =?utf-8?B?cW5aVVJYVlJyNyt1K1RCRWJTckJIaHU2eTE1bGxKSmxKN29uclNsL1JVd3hG?= =?utf-8?B?ZHdUbFNUYkM4c003UzJzRTdXb0ZGMG1RUVlDUkVXU1RwalcvZW1oUjJSaDJv?= =?utf-8?B?SmZTd0tldkc1bnBCL0RPcll4cytjOE90Y3dGQ0JVcisySjlSZXlFcUthVVNR?= =?utf-8?B?VGhnZGlZT09HRkUyMFRoc0k1TmFpelNFNXdGNkpkcXUrRXZrMUdYcUJtRTBu?= =?utf-8?B?QitDbHJYSkZwQUxvaTB4Ym9PRy9odEloc0RlNWovdzRYRXlKS3FYVkJuWlBs?= =?utf-8?B?UlR6SUxJVXloQ3ZYQmU2Umt1bGM4d1JSNXdzQjBJMmEyeUhDMDArbmhVQmFj?= =?utf-8?B?Q1YzYVI4TVhsam5LVWE5Z2pJYVRHa2ozRTRRTDVFY0RkQ1loTVE1dnVWYmdU?= =?utf-8?B?VStzL0tTQktnMWtGMmgrbEJZY1V3TmhhekU1ZnVndGZ5Zm1mSWZNQkJUY1BM?= =?utf-8?B?dlBScVhuZDhuWTBaemIxQXNITEQ1VGlkN3EyMlJkUnVLUk1iMHl3N0JvVy9D?= =?utf-8?B?djBqWjFuTk01Y1krZnZ2eWtLZDV0RXJXQmhXMnE0TFBKUnd1KzlFenpsRkgx?= =?utf-8?B?VHh5LzBHcEt3eCtPQTF0ZlRTalV4YkFWOUpUQVk5aXUyWUczK2VrOXNKdkpx?= =?utf-8?B?a2Q3NTVPSDB1bkxpQ1pCa0pqSHVYQ0tBc3dYOFBYZVE0TW1rcmY2cThFTWpi?= =?utf-8?B?dkxNSkZNbmZwVm0yMUYxc3hUczQvbStqTG1RZThKaFVLUHZRcXJ2WlRrL0Vi?= =?utf-8?B?bUN3eTRsOVZnVnRLRUg0MURLeHdmLzFaRXNRYTVHVllQbEdBMlFKaHVrTlR5?= =?utf-8?B?SnVXa2V4dmJRTFRacTJWVnhRaHlNUzVYajBWalZSVGIyVWhWSGFsakRjZG1I?= =?utf-8?B?QXliUjFLTG5xVjFibi9pOHRrYWdUQ2Y2c1pxbkU5d3FGS3ZtZFQ0amlUVFJn?= =?utf-8?B?V3V0RVFhdXNSaDdhL05RWFlCSHdOK0plS0hnVDcwT2FYZlE5Rmc4clVKUjBE?= =?utf-8?B?bG1kNjdNcHhUT3pNUjdxQXBtbG5SMWswc0o1RGxJQ2VPODFYZUVwaWJhdllE?= =?utf-8?B?bnFKcExJdnBDdVNCVktpL2p2dHlQR0N1WmhTVzB2TjMyR0tDRHhNblY4dGE2?= =?utf-8?B?YkxaVWdZZDZreE1tTWl6alE1TU5MSldZVjFrL0xXcGNEdlhHV1llYmk4aGpm?= =?utf-8?B?cXpqK1ZLZlBVWDQ2MnZReWlPd29yWXBCb1hPdWh4Ty83dXdFdm5raTdaSzBu?= =?utf-8?B?WjRCRVNnLzVIK3d1aVF3TXpZRVNPOXMzaDdwZ00vL3FuNnBYTW1MdTRWbGo1?= =?utf-8?B?ZkxURFFvSUUveTh2Tkl3UndieFMvV2V4N0VBMFdxNHhTZGVVNVE2NytmNjl2?= =?utf-8?B?MWoxc0VXU0xESi9zTWN0d0JKVXVkckhoTVBFMFIvM0VlZFlSQzdvVTc2YWVi?= =?utf-8?B?cXpUN0IwMlU3eWlON3c4U2dVSWRkTXZYd1dha3N0azIrUGFuOUZySnA4SC9K?= =?utf-8?B?MUxOaVJ0VUcvZmE4Z21JNHNHVG5ZeVN1NksyWEhveXNSSTJvV2NJOUdZVFFI?= =?utf-8?B?K3NrVDR0RlQweHVlNmJaZlUrcjBCamtWckE3RjlpbGFpRkRUeUZCZXY0MkJw?= =?utf-8?B?QXFncWwyZE5oazVZY1ZUZ2t0b3FualFIZE5lUmtDcVZJSzlOejQ4YkhRcjBh?= =?utf-8?B?TURPUlpBZDJYRWk1YlVFRmQ4T2tObU1CMmVncDVCTkppbWFjVTFJemsxdm1k?= =?utf-8?B?SE9Rb1ZGYWI2TW1aOWV6REVYM0VaRHZUOWhhY0srTWJ1V0tYUDY1WUlUYWxp?= =?utf-8?Q?t/m3rgq2mOy+dhj9+5NYiUvvN?= Content-Type: multipart/alternative; boundary="_000_17C7D94173CB41BFBE8FBB2788049F1Fglbcorecom_" MIME-Version: 1.0 X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: LV3PR12MB9332.namprd12.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: cf72e424-8764-4fdd-047e-08dcc38b2b35 X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Aug 2024 15:49:27.2238 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 791b26cb-3fdf-47c3-b85d-bd9f037e3e7f X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: MWDYfXHyHnZcKzAuCm4hClNspuhMrFkoAZnHZ88wJaBGHljw1xirufd03pnYwTIWXGOLb7tNgZHgP//cDoRPQQ== X-MS-Exchange-Transport-CrossTenantHeadersStamped: IA0PR12MB8863 X-ProofPoint-Routing: 000e5c18 X-CFilter-Loop: Reflected X-Proofpoint-GUID: Lj8Tf_X95UloBTcsfwHBi0suU_1BGM4T X-Proofpoint-ORIG-GUID: Lj8Tf_X95UloBTcsfwHBi0suU_1BGM4T X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16 definitions=2024-08-23_12,2024-08-22_01,2024-05-17_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 impostorscore=0 suspectscore=0 spamscore=0 adultscore=0 mlxscore=0 phishscore=0 priorityscore=1501 lowpriorityscore=0 mlxlogscore=999 clxscore=1011 malwarescore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2407110000 definitions=main-2408230116 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_17C7D94173CB41BFBE8FBB2788049F1Fglbcorecom_ Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable I have had this issue in the past. The real admin fix to this is to have a NULL replacement character that pre= vents this. This does a few things: 1. An index will index on a replacement character ( I use <->) 2. A join is easier on a replacement character than NULL (Nulls last/fir= st avoided) 3. Stops all evil NULL rules. We strive to fix things, but the real solution, IMHO, is better arch design= and better fundamental understanding of how NULL works. Partitioned tables under 500M-750M rows will always have these performance = issues. Great ideas on the workaround=E2=80=99s though, I do understand sometimes y= ou inherit a bad db. From: Scott Ribe Date: Friday, August 23, 2024 at 8:17=E2=80=AFAM To: Pgsql-admin Subject: Re: checking for a NULL date in a partitioned table kills performa= nce > On Aug 23, 2024, at 5:=E2=80=8A39 AM, Doug Reynolds wrote: > > You could try something really ugly where you m= ake a function index that COALESCEs the nulls to 1-1-1900 and use the COALE= SCE in the query. I don't see > On Aug 23, 2024, at 5:39=E2=80=AFAM, Doug Reynolds wr= ote: > > You could try something really ugly where you make a function index that = COALESCEs the nulls to 1-1-1900 and use the COALESCE in the query. I don't see how that could be better than just creating a partial index on = it WHERE contract_date IS NULL--and anyway I'm sure you're right that no in= dex would help. No matter what, it seems that sequential scans of all parti= tions will be required since most rows have it null, and it's not even rela= ted to the partition key. --_000_17C7D94173CB41BFBE8FBB2788049F1Fglbcorecom_ Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-ID:

I have had this iss= ue in the past.

 

The real admin fix = to this is to have a NULL replacement character that prevents this.

This does a few thi= ngs:

  1. An index will index on a replacement= character ( I use <->)
  2. A join is easier on a replacement character than NULL (Null= s last/first avoided)
  3. Stops all evil NULL rules.

 

We strive to fix th= ings, but the real solution, IMHO, is better arch design and better fundame= ntal understanding of how NULL works.

 

Partitioned tables = under 500M-750M rows will always have these performance issues.<= /span>

 

Great ideas on the = workaround=E2=80=99s though, I do understand sometimes you inherit a bad db= .

 

 

 

From: Scott Ribe <scott_ribe@elevated-dev.com>
Date: Friday, August 23, 2024 at 8:17=E2=80=AFAM
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: checking for a NULL date in a partitioned table kills p= erformance

 

> On Aug 23, 2024, at 5:= =E2=80=8A39 AM, Doug Reynolds <mav@=E2=80=8Awastegate.=E2=80=8Anet> wrote: > > You could try something really ugly where you make a func= tion index that COALESCEs the nulls to 1-1-1900 and use the COALESCE in the= query. I don't see

> On Aug 23, 2024, at 5:39=E2=80=AFAM=
, Doug Reynolds <mav@wastegate.net> wrote:
> 
> You could try something really ugly where you make a function inde=
x that COALESCEs the nulls to 1-1-1900 and use the COALESCE in the query.
 
I don't see how that could be better than just creating a partial index=
 on it WHERE contract_date IS NULL--and anyway I'm sure you're right that n=
o index would help. No matter what, it seems that sequential scans of all p=
artitions will be required since most rows have it null, and it's not even =
related to the partition key.
 
--_000_17C7D94173CB41BFBE8FBB2788049F1Fglbcorecom_--