Re: checking for a NULL date in a partitioned table kills performance

> On Aug 22, 2024, at 8:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Sbob <sbob(at)quadratum-braccas(dot)com> writes:
>> 29 million of the 32 million rows in the table have NULL for contract_date
>
> [ blink... ] So your query is selecting at least 29/32nds of the
> table, plus however much matches the contract_date > '2022-01-01'
> alternative. I'm not sure how you expect that to be significantly
> cheaper than scanning the whole table.
>
> regards, tom lane

^^^ This is best answer; however, what is the actual query and how is it used? I assume it’s a analytical query and not actually extracting the rows. Is for a dashboard or an ad-hoc query?

Here’s simple example; from 4/1 is uses the index 3/1 it does a full table scan. Depending on what you using the query for you could use a covered index or a materialized view.

prod=# create index emp_idx3 on emp (contract_date);
CREATE INDEX
Time: 6036.030 ms (00:06.036)
prod=# select sum(site_id) from emp where contract_date > '4/1/2024';
sum
-----------
927473447
(1 row)

Time: 711.774 ms
prod=# select sum(site_id) from emp where contract_date > '3/1/2024';
sum
------------
1128971203
(1 row)

Time: 1945.397 ms (00:01.945)
prod=# select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null;
sum
------------
3823075309
(1 row)

Time: 1821.284 ms (00:01.821)
prod=# explain select sum(site_id) from emp where contract_date > '4/1/2024';
QUERY PLAN
--------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=754070.31..754070.32 rows=1 width=8)
-> Gather (cost=754069.59..754070.30 rows=7 width=8)
Workers Planned: 7
-> Partial Aggregate (cost=753069.59..753069.60 rows=1 width=8)
-> Parallel Bitmap Heap Scan on emp (cost=5343.20..752867.80 rows=80715 width=4)
Recheck Cond: (contract_date > '2024-04-01'::date)
-> Bitmap Index Scan on emp_idx3 (cost=0.00..5201.95 rows=565002 width=0)
Index Cond: (contract_date > '2024-04-01'::date)
JIT:
Functions: 7
Options: Inlining true, Optimization true, Expressions true, Deforming true
(11 rows)

Time: 1.196 ms
prod=# explain select sum(site_id) from emp where contract_date > '3/1/2024';
QUERY PLAN
---------------------------------------------------------------------------------------
Finalize Aggregate (cost=764566.90..764566.91 rows=1 width=8)
-> Gather (cost=764566.18..764566.89 rows=7 width=8)
Workers Planned: 7
-> Partial Aggregate (cost=763566.18..763566.19 rows=1 width=8)
-> Parallel Seq Scan on emp (cost=0.00..763320.15 rows=98411 width=4)
Filter: (contract_date > '2024-03-01'::date)
JIT:
Functions: 7
Options: Inlining true, Optimization true, Expressions true, Deforming true
(9 rows)

Time: 1.172 ms
prod=# drop index emp_idx3;
DROP INDEX
Time: 8.663 ms
prod=# create index emp_idx3 on emp (contract_date) include (site_id);
CREATE INDEX
Time: 7002.860 ms (00:07.003)
prod=# select sum(site_id) from emp where contract_date > '4/1/2024';
sum
-----------
927473447
(1 row)

Time: 56.450 ms
prod=# select sum(site_id) from emp where contract_date > '3/1/2024';
sum
------------
1128971203
(1 row)

Time: 49.115 ms
prod=# select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null;
sum
------------
3823075309
(1 row)

Time: 702.962 ms
prod=# explain select sum(site_id) from emp where contract_date > '3/1/2024' or contract_date is null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=216035.47..216035.48 rows=1 width=8)
-> Gather (cost=216034.74..216035.45 rows=7 width=8)
Workers Planned: 7
-> Partial Aggregate (cost=215034.74..215034.75 rows=1 width=8)
-> Parallel Index Only Scan using emp_idx3 on emp (cost=0.44..213686.81 rows=539174 width=4)
Filter: ((contract_date > '2024-03-01'::date) OR (contract_date IS NULL))
JIT:
Functions: 5
Options: Inlining false, Optimization false, Expressions true, Deforming true
(9 rows)

Time: 0.972 ms
prod=# select count(*) from emp;
count
----------
16862243
(1 row)

Time: 629.995 ms