All Partitions are hit when predicate contains multiple timestamp columns

[ndchandar]

Hi,
Thank you for this wonderful extension. I have a question that's similar to #382.

I have PG Partman managed table where my predicate is like this

SELECT id, entity, my_bits, my_enum, start_time, end_time from test_table where
("start_time" between TIMESTAMP '2024-08-01 00:00:00.00' and TIMESTAMP '2024-09-01 00:00:00.00' or 
       "end_time" between TIMESTAMP '2024-08-01 00:00:00.00' and TIMESTAMP '2024-09-01 00:00:00.00')
AND entity IN ('my_id_1', 'my_id_2', 'my_id_3')
AND my_enum IN ('VALUE_1', 'VALUE_2');

I have updated my constraints to include all the required predicates. The DDL looks like this

CREATE TABLE IF NOT EXISTS test_table
(
    id               UUID,        
    my_enum          MY_ENUM              NOT NULL,    
    entity           VARCHAR(50)          NOT NULL,
    my_bits          BIT VARYING          NOT NULL,
    start_time       TIMESTAMPTZ          NOT NULL,
    end_time         TIMESTAMPTZ,         NOT NULL,
    created_at       TIMESTAMPTZ          NOT NULL DEFAULT now(),
    CONSTRAINT pk_test_table PRIMARY KEY (id, start_time)
) PARTITION BY RANGE (start_time);

SELECT partman.create_parent(
    p_parent_table := 'test_table',
    p_control := 'start_time',
    p_type => 'native',
    p_interval := '1 day',
    p_premake => 5
);

UPDATE partman.part_config SET constraint_cols = '{"entity",  "end_time", "my_enum"}', optimize_constraint = 4
WHERE parent_table = 'test_table;

But when I generate the plans for this query, it indicates we are hitting all the partitions (child tables). If I I have only "start_time", "entity" and "my_enum" in my predicate then it includes the required partitions correctly. I am testing this with AWS Aurora that uses slightly older version of PGPartman (4.7.3). Recommended setup from RDS - link. I wanted to check if I am missing something or if supporting multiple ranges are not supported currently.

Regards
Dinesh

[keithf4]

Do the child tables actually have the additional constraints added yet?

[ndchandar]

Yes I applied constraints on existing partitions using reapply_constraints_proc (via CALL partman.reapply_constraints_proc('test_table', false, true, 0, false);)

I see "Check constraints" for my_enum, entity and end_time in all the child tables whose partition date was less than optimize_constraint. But when I run the query with end_time predicate and look at the output in Postgresql-explain-visualizer, I see it includes all partitions that are not part of the time window. We have a 90 day retention which makes this range query not an efficient one.