Re: Declarative partitioning - another take

On 2016/09/02 14:38, Ashutosh Bapat wrote:
> Here's something I observed with your set of patches posted in June. I have
> not checked the latest set of patches. So, if it's something fixed, please
> ignore the mail and sorry for me being lazy.
>
> prt1 is partitioned table and it shows following information with \d+
>
> regression=# \d+ prt1
> Partitioned table "public.prt1"
> Column | Type | Modifiers | Storage | Stats target |
> Description
> --------+-------------------+-----------+----------+--------------+-------------
> a | integer | | plain | |
> b | integer | | plain | |
> c | character varying | | extended | |
> Partition Key: PARTITION BY RANGE (a)
> Indexes:
> "iprt1_a" btree (a)
>
> Shouldn't we show all the partitions of this table and may be their ranges
> of lists?

Something I thought about as well. I will implement that.

> I found the partitions from EXPLAIN plan
>
> regression=# explain verbose select * from prt1;
> QUERY PLAN
> -----------------------------------------------------------------------
> Append (cost=0.00..6.00 rows=301 width=13)
> -> Seq Scan on public.prt1 (cost=0.00..0.00 rows=1 width=40)
> Output: prt1.a, prt1.b, prt1.c
> -> Seq Scan on public.prt1_p1 (cost=0.00..2.25 rows=125 width=13)
> Output: prt1_p1.a, prt1_p1.b, prt1_p1.c
> -> Seq Scan on public.prt1_p3 (cost=0.00..1.50 rows=50 width=13)
> Output: prt1_p3.a, prt1_p3.b, prt1_p3.c
> -> Seq Scan on public.prt1_p2 (cost=0.00..2.25 rows=125 width=13)
> Output: prt1_p2.a, prt1_p2.b, prt1_p2.c
> (9 rows)
>
> Then did \d+ on each of those to find their ranges

[ ... ]

>
> As you will observe that the table prt1 can not have any row with a < 0 and
> a > 600. But when I execute
>
> regression=# explain verbose select * from prt1 where a > 1000000;
> QUERY PLAN
> ------------------------------------------------------------------
> Append (cost=0.00..0.00 rows=1 width=40)
> -> Seq Scan on public.prt1 (cost=0.00..0.00 rows=1 width=40)
> Output: prt1.a, prt1.b, prt1.c
> Filter: (prt1.a > 1000000)
> (4 rows)
>
> it correctly excluded all the partitions, but did not exclude the parent
> relation. I guess, we have enough information to exclude it. Probably, we
> should add a check constraint on the parent which is OR of the check
> constraints on all the partitions. So there are two problems here
>
> 1. \d+ doesn't show partitions - this is probably reported earlier, I don't
> remember.

You just did, :)

As I said I will implement that on lines of how inheritance children are
listed (with additional information ie, range or list).

> 2. A combination of constraints on the partitions should be applicable to
> the parent. We aren't doing that.

How about on seeing that a RELOPT_OTHER_MEMBER_REL is partitioned parent
table, we can have get_relation_constraints() include a constant false
clause in the list of constraints returned for
relation_excluded_by_constraints() to process so that it is not included
in the append result by way of constraint exclusion. One more option is
to mark such rels dummy in set_rel_size().

Thanks,
Amit