Horton
Menu

Thread

  1. checking for a NULL date in a partitioned table kills performance

    Sbob <sbob@quadratum-braccas.com> — 2024-08-22T21:44:49Z

    All;

I am running a select from a partitioned table. The table (and all the 
partitions) have an index on contract_date like this:
CREATE INDEX on part_tab (contract_date) where contract_date > 
'2022-01-01'::date

The table (including all partitions) has 32million rows
The db server is an aurora postgresql instance with 128GB of ram and 16 
vcpu's

The shared buffers is set to 90GB and effective_cache_size is also 90GB
I set default_statistics_target to 1000 and ram a vacuum analyze on the 
table

I am selecting a number of columns and specifying this where clause:

WHERE (
                     (contract_date IS NULL)
                     OR
                     (contract_date > '2022-01-01'::date)
              )

This takes 15 seconds to run and an explain says it's doing a table scan 
on all partitions (the query is not specifying the partition key)
If I change the where clause to look like this:

WHERE (
                   (contract_date > '2022-01-01'::date)
              )

Then it performs index scans on all the partitions and runs in about 600ms

If i leave the where clause off entirely it performs table scans of the 
partitions and takes approx 18 seconds to run

I am trying to get the performance to less than 2sec,
I have tried adding indexes on the table and all partitions like this:
CREATE INDEX ON table (contract_date NULLS FIRST) ;
but the performance with the full where clause is the same:

WHERE (
                     (contract_date IS NULL)
                     OR
                     (contract_date > '2022-01-01'::date)
              )

runs in 15 seconds and scans all partitions

I also tried indexes i=on the table and all partitions like this:
CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;

but I get the same result, table scans on all partitions and it runs in 
15 seconds

Any help or advice ?

Thanks in advance

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

    Rui DeSousa <rui.desousa@icloud.com> — 2024-08-22T23:06:10Z

    
> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
> 
> All;
> 
> I am running a select from a partitioned table. The table (and all the partitions) have an index on contract_date like this:
> CREATE INDEX on part_tab (contract_date) where contract_date > '2022-01-01'::date
> 
> The table (including all partitions) has 32million rows
> The db server is an aurora postgresql instance with 128GB of ram and 16 vcpu's
> 
> The shared buffers is set to 90GB and effective_cache_size is also 90GB
> I set default_statistics_target to 1000 and ram a vacuum analyze on the table
> 
> I am selecting a number of columns and specifying this where clause:
> 
> WHERE (
>                     (contract_date IS NULL)
>                     OR
>                     (contract_date > '2022-01-01'::date)
>              )
> 
> This takes 15 seconds to run and an explain says it's doing a table scan on all partitions (the query is not specifying the partition key)
> If I change the where clause to look like this:
> 
> WHERE (
>                   (contract_date > '2022-01-01'::date)
>              )
> 
> Then it performs index scans on all the partitions and runs in about 600ms
> 
> If i leave the where clause off entirely it performs table scans of the partitions and takes approx 18 seconds to run
> 
> I am trying to get the performance to less than 2sec,
> I have tried adding indexes on the table and all partitions like this:
> CREATE INDEX ON table (contract_date NULLS FIRST) ;
> but the performance with the full where clause is the same:
> 
> WHERE (
>                     (contract_date IS NULL)
>                     OR
>                     (contract_date > '2022-01-01'::date)
>              )
> 
> runs in 15 seconds and scans all partitions
> 
> I also tried indexes i=on the table and all partitions like this:
> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
> 
> but I get the same result, table scans on all partitions and it runs in 15 seconds
> 
> Any help or advice ?
> 
> Thanks in advance
> 
> 

What is contract_date and when will it be null?

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

    Sbob <sbob@quadratum-braccas.com> — 2024-08-22T23:26:22Z

    On 8/22/24 5:06 PM, Rui DeSousa wrote:
>
>> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>
>> All;
>>
>> I am running a select from a partitioned table. The table (and all the partitions) have an index on contract_date like this:
>> CREATE INDEX on part_tab (contract_date) where contract_date > '2022-01-01'::date
>>
>> The table (including all partitions) has 32million rows
>> The db server is an aurora postgresql instance with 128GB of ram and 16 vcpu's
>>
>> The shared buffers is set to 90GB and effective_cache_size is also 90GB
>> I set default_statistics_target to 1000 and ram a vacuum analyze on the table
>>
>> I am selecting a number of columns and specifying this where clause:
>>
>> WHERE (
>>                      (contract_date IS NULL)
>>                      OR
>>                      (contract_date > '2022-01-01'::date)
>>               )
>>
>> This takes 15 seconds to run and an explain says it's doing a table scan on all partitions (the query is not specifying the partition key)
>> If I change the where clause to look like this:
>>
>> WHERE (
>>                    (contract_date > '2022-01-01'::date)
>>               )
>>
>> Then it performs index scans on all the partitions and runs in about 600ms
>>
>> If i leave the where clause off entirely it performs table scans of the partitions and takes approx 18 seconds to run
>>
>> I am trying to get the performance to less than 2sec,
>> I have tried adding indexes on the table and all partitions like this:
>> CREATE INDEX ON table (contract_date NULLS FIRST) ;
>> but the performance with the full where clause is the same:
>>
>> WHERE (
>>                      (contract_date IS NULL)
>>                      OR
>>                      (contract_date > '2022-01-01'::date)
>>               )
>>
>> runs in 15 seconds and scans all partitions
>>
>> I also tried indexes i=on the table and all partitions like this:
>> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>>
>> but I get the same result, table scans on all partitions and it runs in 15 seconds
>>
>> Any help or advice ?
>>
>> Thanks in advance
>>
>>
> What is contract_date and when will it be null?


it's a date data type and it allows NULL's not sure why, this is a 
client's system

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

    Sbob <sbob@quadratum-braccas.com> — 2024-08-22T23:32:18Z

    On 8/22/24 5:26 PM, Sbob wrote:
>
> On 8/22/24 5:06 PM, Rui DeSousa wrote:
>>
>>> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>>
>>> All;
>>>
>>> I am running a select from a partitioned table. The table (and all 
>>> the partitions) have an index on contract_date like this:
>>> CREATE INDEX on part_tab (contract_date) where contract_date > 
>>> '2022-01-01'::date
>>>
>>> The table (including all partitions) has 32million rows
>>> The db server is an aurora postgresql instance with 128GB of ram and 
>>> 16 vcpu's
>>>
>>> The shared buffers is set to 90GB and effective_cache_size is also 90GB
>>> I set default_statistics_target to 1000 and ram a vacuum analyze on 
>>> the table
>>>
>>> I am selecting a number of columns and specifying this where clause:
>>>
>>> WHERE (
>>>                      (contract_date IS NULL)
>>>                      OR
>>>                      (contract_date > '2022-01-01'::date)
>>>               )
>>>
>>> This takes 15 seconds to run and an explain says it's doing a table 
>>> scan on all partitions (the query is not specifying the partition key)
>>> If I change the where clause to look like this:
>>>
>>> WHERE (
>>>                    (contract_date > '2022-01-01'::date)
>>>               )
>>>
>>> Then it performs index scans on all the partitions and runs in about 
>>> 600ms
>>>
>>> If i leave the where clause off entirely it performs table scans of 
>>> the partitions and takes approx 18 seconds to run
>>>
>>> I am trying to get the performance to less than 2sec,
>>> I have tried adding indexes on the table and all partitions like this:
>>> CREATE INDEX ON table (contract_date NULLS FIRST) ;
>>> but the performance with the full where clause is the same:
>>>
>>> WHERE (
>>>                      (contract_date IS NULL)
>>>                      OR
>>>                      (contract_date > '2022-01-01'::date)
>>>               )
>>>
>>> runs in 15 seconds and scans all partitions
>>>
>>> I also tried indexes i=on the table and all partitions like this:
>>> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>>>
>>> but I get the same result, table scans on all partitions and it runs 
>>> in 15 seconds
>>>
>>> Any help or advice ?
>>>
>>> Thanks in advance
>>>
>>>
>> What is contract_date and when will it be null?
>
>
> it's a date data type and it allows NULL's not sure why, this is a 
> client's system
>
>
29 million of the 32 million rows in the table have NULL for contract_date

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

    David G. Johnston <david.g.johnston@gmail.com> — 2024-08-23T00:01:37Z

    On Thu, Aug 22, 2024 at 4:32 PM Sbob <sbob@quadratum-braccas.com> wrote:

>
> 29 million of the 32 million rows in the table have NULL for contract_date
>
>
Your expectation that this query should use an index is flawed.  Indexes
are for highly selective queries.  Finding nulls on that table is not
selective.

David J.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-08-23T00:05:19Z

    Sbob <sbob@quadratum-braccas.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

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

    Rui DeSousa <rui.desousa@icloud.com> — 2024-08-23T00:07:55Z

    
> On Aug 22, 2024, at 7:32 PM, Sbob <sbob@quadratum-braccas.com> wrote:
> 
> 
> On 8/22/24 5:26 PM, Sbob wrote:
>> 
>> On 8/22/24 5:06 PM, Rui DeSousa wrote:
>>> 
>>>> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>>> 
>>>> All;
>>>> 
>>>> I am running a select from a partitioned table. The table (and all the partitions) have an index on contract_date like this:
>>>> CREATE INDEX on part_tab (contract_date) where contract_date > '2022-01-01'::date
>>>> 
>>>> The table (including all partitions) has 32million rows
>>>> The db server is an aurora postgresql instance with 128GB of ram and 16 vcpu's
>>>> 
>>>> The shared buffers is set to 90GB and effective_cache_size is also 90GB
>>>> I set default_statistics_target to 1000 and ram a vacuum analyze on the table
>>>> 
>>>> I am selecting a number of columns and specifying this where clause:
>>>> 
>>>> WHERE (
>>>>                      (contract_date IS NULL)
>>>>                      OR
>>>>                      (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> This takes 15 seconds to run and an explain says it's doing a table scan on all partitions (the query is not specifying the partition key)
>>>> If I change the where clause to look like this:
>>>> 
>>>> WHERE (
>>>>                    (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> Then it performs index scans on all the partitions and runs in about 600ms
>>>> 
>>>> If i leave the where clause off entirely it performs table scans of the partitions and takes approx 18 seconds to run
>>>> 
>>>> I am trying to get the performance to less than 2sec,
>>>> I have tried adding indexes on the table and all partitions like this:
>>>> CREATE INDEX ON table (contract_date NULLS FIRST) ;
>>>> but the performance with the full where clause is the same:
>>>> 
>>>> WHERE (
>>>>                      (contract_date IS NULL)
>>>>                      OR
>>>>                      (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> runs in 15 seconds and scans all partitions
>>>> 
>>>> I also tried indexes i=on the table and all partitions like this:
>>>> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>>>> 
>>>> but I get the same result, table scans on all partitions and it runs in 15 seconds
>>>> 
>>>> Any help or advice ?
>>>> 
>>>> Thanks in advance
>>>> 
>>>> 
>>> What is contract_date and when will it be null?
>> 
>> 
>> it's a date data type and it allows NULL's not sure why, this is a client's system
>> 
>> 
> 29 million of the 32 million rows in the table have NULL for contract_date
> 

NULLs are not indexed thus the OR predicate invalidate the use of the index.  

Since you are already creating a partial index just include the NULLs.  It index will get used for both of your queries.

create index table_idx1 
  on table (contract_date) 
  where contract_date > ‘1/1/2022’
      or contract_date is null
;


The reason why I asked when is contract_date null is because attributes in a table should be non nullable.  If it’s nullable then that begs the question if it belong in that table in the first place; and sometimes the answer is yes.  I just see a lot of half baked schemas out there.  I refer to them as organically designed schemas. 

-Rui.

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

    Muhammad Usman Khan <usman.k@bitnine.net> — 2024-08-23T04:40:28Z

    Hi Sbob,
Have you tried using the following indexes ?

B-tree

   -

      The default and most commonly used type, ideal for equality, range
      and Pattern queries.

BRIN (Block Range INdex)

   -

      Compact indexes that are efficient for large tables where the data is
      naturally ordered.



On Fri, 23 Aug 2024 at 02:45, Sbob <sbob@quadratum-braccas.com> wrote:

> All;
>
> I am running a select from a partitioned table. The table (and all the
> partitions) have an index on contract_date like this:
> CREATE INDEX on part_tab (contract_date) where contract_date >
> '2022-01-01'::date
>
> The table (including all partitions) has 32million rows
> The db server is an aurora postgresql instance with 128GB of ram and 16
> vcpu's
>
> The shared buffers is set to 90GB and effective_cache_size is also 90GB
> I set default_statistics_target to 1000 and ram a vacuum analyze on the
> table
>
> I am selecting a number of columns and specifying this where clause:
>
> WHERE (
>                      (contract_date IS NULL)
>                      OR
>                      (contract_date > '2022-01-01'::date)
>               )
>
> This takes 15 seconds to run and an explain says it's doing a table scan
> on all partitions (the query is not specifying the partition key)
> If I change the where clause to look like this:
>
> WHERE (
>                    (contract_date > '2022-01-01'::date)
>               )
>
> Then it performs index scans on all the partitions and runs in about 600ms
>
> If i leave the where clause off entirely it performs table scans of the
> partitions and takes approx 18 seconds to run
>
> I am trying to get the performance to less than 2sec,
> I have tried adding indexes on the table and all partitions like this:
> CREATE INDEX ON table (contract_date NULLS FIRST) ;
> but the performance with the full where clause is the same:
>
> WHERE (
>                      (contract_date IS NULL)
>                      OR
>                      (contract_date > '2022-01-01'::date)
>               )
>
> runs in 15 seconds and scans all partitions
>
> I also tried indexes i=on the table and all partitions like this:
> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>
> but I get the same result, table scans on all partitions and it runs in
> 15 seconds
>
> Any help or advice ?
>
> Thanks in advance
>
>
>

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

    shammat@gmx.net — 2024-08-23T06:45:44Z

    Sbob schrieb am 22.08.2024 um 23:44:
> 
> I am selecting a number of columns and specifying this where clause:
> 
> WHERE (
>                     (contract_date IS NULL)
>                     OR
>                     (contract_date > '2022-01-01'::date)
>              )
> 

It's not the check for NULL, it's the OR that makes this perform so badly. 

I typically never set columns used for range queries to NULL. 

Would using infinity instead of NULL be a viable option here? 

Then you can remove the OR condition entirely.

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

    Rui DeSousa <rui.desousa@icloud.com> — 2024-08-23T09:54:51Z

    
> On Aug 22, 2024, at 8:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> Sbob <sbob@quadratum-braccas.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

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

    Doug Reynolds <mav@wastegate.net> — 2024-08-23T11:39:05Z

    I don’t see how an index is going to help since virtually all of the rows are null AND contract_date isn’t the partition key.  

Perhaps, you could try a UNION ALL with one query selecting the date and the other selecting where the date is null. 

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.


Sent from my iPhone

> On Aug 22, 2024, at 7:43 PM, Sbob <sbob@quadratum-braccas.com> wrote:
> 
> 
>> On 8/22/24 5:26 PM, Sbob wrote:
>> 
>>> On 8/22/24 5:06 PM, Rui DeSousa wrote:
>>> 
>>>> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>>> 
>>>> All;
>>>> 
>>>> I am running a select from a partitioned table. The table (and all the partitions) have an index on contract_date like this:
>>>> CREATE INDEX on part_tab (contract_date) where contract_date > '2022-01-01'::date
>>>> 
>>>> The table (including all partitions) has 32million rows
>>>> The db server is an aurora postgresql instance with 128GB of ram and 16 vcpu's
>>>> 
>>>> The shared buffers is set to 90GB and effective_cache_size is also 90GB
>>>> I set default_statistics_target to 1000 and ram a vacuum analyze on the table
>>>> 
>>>> I am selecting a number of columns and specifying this where clause:
>>>> 
>>>> WHERE (
>>>>                      (contract_date IS NULL)
>>>>                      OR
>>>>                      (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> This takes 15 seconds to run and an explain says it's doing a table scan on all partitions (the query is not specifying the partition key)
>>>> If I change the where clause to look like this:
>>>> 
>>>> WHERE (
>>>>                    (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> Then it performs index scans on all the partitions and runs in about 600ms
>>>> 
>>>> If i leave the where clause off entirely it performs table scans of the partitions and takes approx 18 seconds to run
>>>> 
>>>> I am trying to get the performance to less than 2sec,
>>>> I have tried adding indexes on the table and all partitions like this:
>>>> CREATE INDEX ON table (contract_date NULLS FIRST) ;
>>>> but the performance with the full where clause is the same:
>>>> 
>>>> WHERE (
>>>>                      (contract_date IS NULL)
>>>>                      OR
>>>>                      (contract_date > '2022-01-01'::date)
>>>>               )
>>>> 
>>>> runs in 15 seconds and scans all partitions
>>>> 
>>>> I also tried indexes i=on the table and all partitions like this:
>>>> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>>>> 
>>>> but I get the same result, table scans on all partitions and it runs in 15 seconds
>>>> 
>>>> Any help or advice ?
>>>> 
>>>> Thanks in advance
>>>> 
>>>> 
>>> What is contract_date and when will it be null?
>> 
>> 
>> it's a date data type and it allows NULL's not sure why, this is a client's system
>> 
>> 
> 29 million of the 32 million rows in the table have NULL for contract_date
> 
> 
> 
> 
> 
>

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

    Sbob <sbob@quadratum-braccas.com> — 2024-08-23T13:47:00Z

    On 8/22/24 6:07 PM, Rui DeSousa wrote:
>
>
>> On Aug 22, 2024, at 7:32 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>
>>
>> On 8/22/24 5:26 PM, Sbob wrote:
>>>
>>> On 8/22/24 5:06 PM, Rui DeSousa wrote:
>>>>
>>>>> On Aug 22, 2024, at 5:44 PM, Sbob <sbob@quadratum-braccas.com> wrote:
>>>>>
>>>>> All;
>>>>>
>>>>> I am running a select from a partitioned table. The table (and all 
>>>>> the partitions) have an index on contract_date like this:
>>>>> CREATE INDEX on part_tab (contract_date) where contract_date > 
>>>>> '2022-01-01'::date
>>>>>
>>>>> The table (including all partitions) has 32million rows
>>>>> The db server is an aurora postgresql instance with 128GB of ram 
>>>>> and 16 vcpu's
>>>>>
>>>>> The shared buffers is set to 90GB and effective_cache_size is also 
>>>>> 90GB
>>>>> I set default_statistics_target to 1000 and ram a vacuum analyze 
>>>>> on the table
>>>>>
>>>>> I am selecting a number of columns and specifying this where clause:
>>>>>
>>>>> WHERE (
>>>>>                      (contract_date IS NULL)
>>>>>                      OR
>>>>>                      (contract_date > '2022-01-01'::date)
>>>>>               )
>>>>>
>>>>> This takes 15 seconds to run and an explain says it's doing a 
>>>>> table scan on all partitions (the query is not specifying the 
>>>>> partition key)
>>>>> If I change the where clause to look like this:
>>>>>
>>>>> WHERE (
>>>>>                    (contract_date > '2022-01-01'::date)
>>>>>               )
>>>>>
>>>>> Then it performs index scans on all the partitions and runs in 
>>>>> about 600ms
>>>>>
>>>>> If i leave the where clause off entirely it performs table scans 
>>>>> of the partitions and takes approx 18 seconds to run
>>>>>
>>>>> I am trying to get the performance to less than 2sec,
>>>>> I have tried adding indexes on the table and all partitions like this:
>>>>> CREATE INDEX ON table (contract_date NULLS FIRST) ;
>>>>> but the performance with the full where clause is the same:
>>>>>
>>>>> WHERE (
>>>>>                      (contract_date IS NULL)
>>>>>                      OR
>>>>>                      (contract_date > '2022-01-01'::date)
>>>>>               )
>>>>>
>>>>> runs in 15 seconds and scans all partitions
>>>>>
>>>>> I also tried indexes i=on the table and all partitions like this:
>>>>> CREATE INDEX ON table (contract_date) WHERE contract_date IS NULL;
>>>>>
>>>>> but I get the same result, table scans on all partitions and it 
>>>>> runs in 15 seconds
>>>>>
>>>>> Any help or advice ?
>>>>>
>>>>> Thanks in advance
>>>>>
>>>>>
>>>> What is contract_date and when will it be null?
>>>
>>>
>>> it's a date data type and it allows NULL's not sure why, this is a 
>>> client's system
>>>
>>>
>> 29 million of the 32 million rows in the table have NULL for 
>> contract_date
>>
>
> NULLs are not indexed thus the OR predicate invalidate the use of the 
> index.
>
> Since you are already creating a partial index just include the NULLs. 
>  It index will get used for both of your queries.
>
> create index table_idx1
>   on table (contract_date)
>   where contract_date > ‘1/1/2022’
>       or contract_date is null
> ;
>
>
> The reason why I asked when is contract_date null is because 
> attributes in a table should be non nullable.  If it’s nullable then 
> that begs the question if it belong in that table in the first place; 
> and sometimes the answer is yes.  I just see a lot of half baked 
> schemas out there.  I refer to them as organically designed schemas.
>
> -Rui.
>
I agree, I will find out from the client

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

    Scott Ribe <scott_ribe@elevated-dev.com> — 2024-08-23T15:17:19Z

    > 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.

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

    Doug Reynolds <mav@wastegate.net> — 2024-08-23T15:42:16Z

    The only difference is that you would be reading from one index instead of two, which could be more efficient. 


Sent from my iPhone

> On Aug 23, 2024, at 11:19 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> 
> 
>> 
>> 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.
>

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

    Wetmore, Matthew (CTR) <matthew.wetmore@mdlive.com> — 2024-08-23T15:49:27Z

    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:

  1.  An index will index on a replacement character ( I use <->)
  2.  A join is easier on a replacement character than NULL (Nulls last/first 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’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.

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

    Scott Ribe <scott_ribe@elevated-dev.com> — 2024-08-23T16:08:49Z

    > On Aug 23, 2024, at 9:42 AM, Doug Reynolds <mav@wastegate.net> wrote:
> 
> The only difference is that you would be reading from one index instead of two, which could be more efficient. 

Ah yes, that's a good point to take into consideration in such a case.

In the one at hand though, if statistics are correct, neither index is going to be used, given the 90% of rows with NULL values. Using an index would just waste time compared to a simple sequential scan.