On 19 October 2018 at 02:49, Dinko Papak <rimokatolik@xxxxxxxxxxx> 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
