Help with list partitioning on expression

[Dinko Papak]

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

RE: Help with list partitioning on expression

[Dinko Papak]

Thank you! Thank you! Thank you!



I would not have figured it out, but solution is so simple.



Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10




From: David Rowley 
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

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

Are indices used for creating check constraints?

[Dinko Papak]

Here are 3 interesting (to me) numbers:

1. creating index on expression (func(timestamp)) takes 5 seconds

2. creating check constraint on the same expression takes 10 seconds

3. adding partition table based on the same expression without check expression 
takes 20 seconds (this has been resolved by answer from David Rowley)


I tried various kind of indices to optimize 2. but nothing helps. When creating 
check constraint on column values directly is very fast (seems analyze helps), 
but creating check constraint on expression is very slow, even twice as long as 
creating index on the same expression.


Is it possible to use indices to create check expressions?

Why is 2. twice as long as 1.?

Why is 3. twice as long as 2.? (when there is no appropriate check constraint 
for 3.)


Thank you,

Dinko


Sent from Outlook