I've done a simple test case, and find out that probably the problem I
got was due to the partition schema I'm using.
I want a table to be partitioned by a timestamp field with a first
level partition by year, and a second level by month. Therefore, I did
a BY LIST partitioning, but that produces a wrong constraint check
when executing a query.
This is a reproducible example.
BEGIN;
CREATE TABLE root( pk int generated always as identity, v int, ts
timestamp default current_timestamp )
PARTITION BY LIST( extract( year from ts ) );
CREATE TABLE y2018
PARTITION OF root
FOR VALUES IN ( 2018 );
CREATE TABLE y2019
PARTITION OF root
FOR VALUES IN ( 2019 );
ALTER TABLE y2018 ADD CHECK( ts >= make_timestamp( 2018, 1, 1, 0, 0, 0 )
AND ts <= make_timestamp( 2018, 12, 31, 23, 59, 59 ) );
ALTER TABLE y2019 ADD CHECK( ts >= make_timestamp( 2019, 1, 1, 0, 0, 0 )
AND ts <= make_timestamp( 2019, 12, 31, 23, 59, 59 ) );
INSERT INTO root( v )
SELECT generate_series( 1, 100 ); -- same ts here
COMMIT;
Now if I try to explain a query with the current timestamp (which is
of course in 2019):
testdb=# explain select * from root where ts = current_timestamp;
QUERY PLAN
-------------------------------------------------------------
Append (cost=0.00..75.59 rows=18 width=16)
-> Seq Scan on y2018 (cost=0.00..37.75 rows=9 width=16)
Filter: (ts = CURRENT_TIMESTAMP)
-> Seq Scan on y2019 (cost=0.00..37.75 rows=9 width=16)
Filter: (ts = CURRENT_TIMESTAMP)
(5 rows)
I got y2018 scanned too, which of course could not be the case since
y2018 cannot contain values that are equal to current_timestamp.
However, if I use a literal the query works fine:
testdb=# explain select * from root where ts = '2019-09-01 09:00:00.000000';
QUERY PLAN
---------------------------------------------------------------------------
Append (cost=0.00..33.17 rows=9 width=16)
-> Seq Scan on y2019 (cost=0.00..33.12 rows=9 width=16)
Filter: (ts = '2019-09-01 09:00:00'::timestamp without time zone)
(3 rows)
Now, if I change the partition schema using a range, the query works
fine with current_timestamp too:
CREATE TABLE root( pk int generated always as identity, v int, ts
timestamp default current_timestamp )
PARTITION BY RANGE( ts );
CREATE TABLE y2018
PARTITION OF root
FOR VALUES FROM ('2018-01-01 00:00:00.000000')
TO ('2018-12-31 23:59:59.000000');
CREATE TABLE y2019
PARTITION OF root
FOR VALUES FROM ('2019-01-01 00:00:00.000000')
TO ('2019-12-31 23:59:59.000000');
testdb=# explain select * from root where ts = current_timestamp;
QUERY PLAN
-------------------------------------------------------------
Append (cost=0.00..75.59 rows=18 width=16)
Subplans Removed: 1
-> Seq Scan on y2019 (cost=0.00..37.75 rows=9 width=16)
Filter: (ts = CURRENT_TIMESTAMP)
(4 rows)
So my end with this is that:
- the list partitioning prevents the current_timestamp to be compared
against the list of possible values (extract year from
current_timestamp) and therefore the planner has no chance but to get
into all the tables, even if the constraints on the ts field
explicitly state some tables can be removed;
- in range partitioning, since the partition is built on the very
range of values, the planner gets the correct path.
I still don't get why using a literal in the first case can lead to a
"more correct" plan.
And I'm curious to know if there's a way to force constraints in the
list partitioning to make the planner really aware of tables that can
be excluded.
Luca
