I have had this issue in the past.
The real admin fix to this is to have a NULL replacement character that prevents this.
This does a few things:
- An index will index on a replacement character ( I use <->)
- A join is easier on a replacement character than NULL (Nulls last/first avoided)
- 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’s 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 AM
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: checking for a NULL date in a partitioned table kills performance
> On Aug 23, 2024, at 5: 39 AM, Doug Reynolds <mav@ wastegate. net> wrote: > > 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
> On Aug 23, 2024, at 5:39 AM, Doug Reynolds <mav@wastegate.net> wrote:
>
> 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 index would help. No matter what, it seems that sequential scans of all partitions will be required since most rows have it null, and it's not even related to the partition key.
