On Thu, Aug 29, 2019 at 4:45 PM Luca Ferrari <fluca1978@gmail.com> wrote:
>
> Ok, the title is a little buzz, however I've got a partitioned table
> and one "leaf" has a set of checks against a timestamp field to ensure
> that tuples within such table belongs to the year and month:
Of course, all the siblings have similar constraints. So my partition
starts at a table named "root", then it it has a level for the year,
and each year has subpartitions for months:
- root
- y2018
- y2018m01, y2018m02, ...
- y2019
- y2019m01, y2019m02 ....
All partitions have been created equally, and constraints seem fine to me:
testdb=# \d respi.y2018m01
...
Partition of: respi.y2018 FOR VALUES IN ('1')
Check constraints:
"y2018_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
2018::double precision)
"y2018_mis_ora_check1" CHECK (mis_ora >= make_timestamp(2018, 1,
1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2018, 12,
31, 23, 59, 59::double precision))
"y2018m01_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
2018::double precision)
"y2018m01_mis_ora_check1" CHECK (mis_ora >= make_timestamp(2018,
1, 1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2018,
1, 31, 23, 59, 59::double precision))
testdb=># \d+ respi.y2018
...
Partition of: respi.root FOR VALUES IN ('2018')
Partition constraint: ((date_part('year'::text, mis_ora) IS NOT NULL)
AND (date_part('year'::text, mis_ora) = '2018'::double precision))
Partition key: LIST (date_part('month'::text, mis_ora))
Check constraints:
"y2018_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
2018::double precision)
"y2018_mis_ora_check1" CHECK (mis_ora >= make_timestamp(2018, 1,
1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2018, 12,
31, 23, 59, 59::double precision))
Partitions: respi.y2018m01 FOR VALUES IN ('1'),
respi.y2018m02 FOR VALUES IN ('2'),
respi.y2018m03 FOR VALUES IN ('3'),
respi.y2018m04 FOR VALUES IN ('4'),
respi.y2018m05 FOR VALUES IN ('5'),
respi.y2018m06 FOR VALUES IN ('6'),
respi.y2018m07 FOR VALUES IN ('7'),
respi.y2018m08 FOR VALUES IN ('8'),
respi.y2018m09 FOR VALUES IN ('9'),
...
With the above constraint, all the branch starting at y2018 should be
excluded when selecting with
mis_ora >= CURRENT_TIMESTAMP
(the date of the server is right, of course).
Why is instead scanned (as reported by the execution plan in the
previous email)?
Thanks,
Luca
