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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
