Обсуждение: Help with list partitioning on expression
I have created table partitioned by list on expression using timestamp column, so for each timestamp value function returns int and each partition table is for single value (range of timestamp for which function result is the same). This helps me to simplify querying as I do not need to always write date ranges. Querying (partition pruning) works nice, however when I attach new partition it seems to always scan whole table, although I do have necessary check constraint on partitioned table. I have tried to make timestamp column both null and not null. Also, it takes longer to attach partition then to add constraint itself although per my understanding those 2 operations should do the same scan.
Thank you,
Dinko
Sent from Mail for Windows 10
On 19 October 2018 at 02:49, Dinko Papak <rimokatolik@outlook.com> wrote: > I have created table partitioned by list on expression using timestamp > column, so for each timestamp value function returns int and each partition > table is for single value (range of timestamp for which function result is > the same). This helps me to simplify querying as I do not need to always > write date ranges. Querying (partition pruning) works nice, however when I > attach new partition it seems to always scan whole table, although I do have > necessary check constraint on partitioned table. I have tried to make > timestamp column both null and not null. Also, it takes longer to attach > partition then to add constraint itself although per my understanding those > 2 operations should do the same scan. It's not all that obvious, but if you have PARTITION BY LIST (extract(minute FROM ts)) and try to attach a partition like: CREATE TABLE mypartition PARTITION OF parted FOR VALUES IN (1); then the partition constraint is actually (extract(minute FROM ts) IS NOT NULL AND extract(minute FROM ts) = 1). If your CHECK constraint is just checking `extract(minute FROM ts) = 1` then the current code in PartConstraintImpliedByRelConstraint() is not smart enough to know that `extract(minute FROM ts) = 1` is strict and cannot match nulls. Perhaps that could be improved, but that's how it is today. Likely you'll have better luck with a check constraint that explicitly checks the function IS NOT NULL. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Thank you! Thank you! Thank you!
I would not have figured it out, but solution is so simple.
Sent from Mail for Windows 10
Sent: Sunday, October 21, 2018 6:50:47 PM
To: Dinko Papak
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Help with list partitioning on expression
> I have created table partitioned by list on expression using timestamp
> column, so for each timestamp value function returns int and each partition
> table is for single value (range of timestamp for which function result is
> the same). This helps me to simplify querying as I do not need to always
> write date ranges. Querying (partition pruning) works nice, however when I
> attach new partition it seems to always scan whole table, although I do have
> necessary check constraint on partitioned table. I have tried to make
> timestamp column both null and not null. Also, it takes longer to attach
> partition then to add constraint itself although per my understanding those
> 2 operations should do the same scan.
It's not all that obvious, but if you have PARTITION BY LIST
(extract(minute FROM ts)) and try to attach a partition like:
CREATE TABLE mypartition PARTITION OF parted FOR VALUES IN (1);
then the partition constraint is actually (extract(minute FROM ts) IS
NOT NULL AND extract(minute FROM ts) = 1). If your CHECK constraint is
just checking `extract(minute FROM ts) = 1` then the current code in
PartConstraintImpliedByRelConstraint() is not smart enough to know
that `extract(minute FROM ts) = 1` is strict and cannot match nulls.
Perhaps that could be improved, but that's how it is today.
Likely you'll have better luck with a check constraint that explicitly
checks the function IS NOT NULL.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services