Guys, I am sorry for taking your time. The reason for my question is: As former Oracle DBA and now simple beginner PostgreSQL DBA I would like to say: the current partitioning mechanism might be improved. Sorry, it seems to me far behind yesterday requirements. As model for improvement the Oracle might be taken as example. Unfortunately I am not writing an C code and see my benefit to PostgreSQL community in only rising this issue. I'll be very happy to be helpful in something else, but...
Sincerely yours, Yuri Levinsky, DBA Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222 -----Original Message----- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Tuesday, June 25, 2013 6:55 PM To: Bruce Momjian Cc: Yuri Levinsky; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Hash partitioning. On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian <br...@momjian.us> wrote: > On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote: >> On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian <br...@momjian.us> wrote: >> >> Not really. Constraint exclusion won't kick in for a constraint >> >> like CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42. >> > >> > Uh, I thought we checked the constant against every CHECK >> > constraint and only scanned partitions that matched. Why does this not work? >> >> That's a pretty fuzzy description of what we do. For this to work, >> we'd have to be able to use the predicate a = 42 to prove that >> hashme(a) % 16 = 3 is false. But we can't actually substitute 42 in >> for a and then evaluate hashme(42) % 16 = 3, because we don't know >> that the a = 42 in the WHERE clause means exact equality for all >> purposes, only that it means "has the numerically same value". For >> integers, equality under = is sufficient to prove equivalence. >> >> But for numeric values, for example, it is not. The values >> '42'::numeric and '42.0'::numeric are equal according to =(numeric, >> numeric), but they are not the same. If the hashme() function did >> something like length($1::text), it would get different answers for >> those two values. IOW, the theorem prover has no way of knowing that >> the hash function provided has semantics that are compatible with the >> opclass of the operator used in the query. > > I looked at predtest.c but I can't see how we accept >= and <= ranges, > but not CHECK (a % 16 == 3). It is the '%' operator? I am not sure > why the hashme() function is there. Wouldn't it work if hashme() was > an immutable function? Let me back up a minute. You told the OP that he could make hash partitioning by writing his own constraint and trigger functions. I think that won't work. But I'm happy to be proven wrong. Do you have an example showing how to do it? Here's why I think it WON'T work: rhaas=# create table foo (a int, b text); CREATE TABLE rhaas=# create table foo0 (check ((a % 16) = 0)) inherits (foo); CREATE TABLE rhaas=# create table foo1 (check ((a % 16) = 1)) inherits (foo); CREATE TABLE rhaas=# create table foo2 (check ((a % 16) = 2)) inherits (foo); CREATE TABLE rhaas=# create table foo3 (check ((a % 16) = 3)) inherits (foo); CREATE TABLE rhaas=# explain select * from foo where a = 1; QUERY PLAN ------------------------------------------------------------ Append (cost=0.00..101.50 rows=25 width=36) -> Seq Scan on foo (cost=0.00..0.00 rows=1 width=36) Filter: (a = 1) -> Seq Scan on foo0 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) -> Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) -> Seq Scan on foo2 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) -> Seq Scan on foo3 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) (11 rows) Notice we get a scan on every partition. Now let's try it with no modulo arithmetic, just a straightforward one-partition-per-value: rhaas=# create table foo (a int, b text); CREATE TABLE rhaas=# create table foo0 (check (a = 0)) inherits (foo); CREATE TABLE rhaas=# create table foo1 (check (a = 1)) inherits (foo); CREATE TABLE rhaas=# create table foo2 (check (a = 2)) inherits (foo); CREATE TABLE rhaas=# create table foo3 (check (a = 3)) inherits (foo); CREATE TABLE rhaas=# explain select * from foo where a = 1; QUERY PLAN ------------------------------------------------------------ Append (cost=0.00..25.38 rows=7 width=36) -> Seq Scan on foo (cost=0.00..0.00 rows=1 width=36) Filter: (a = 1) -> Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) (5 rows) Voila, now constraint exclusion is working. I confess that I'm not entirely clear about the details either, but the above tests speak for themselves. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company This mail was received via Mail-SeCure System. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers