Re: Hash partitioning.
От | Robert Haas |
---|---|
Тема | Re: Hash partitioning. |
Дата | |
Msg-id | CA+TgmoaE9NZ_RiqZQLp2aJXPO4E78QxkQYL-FR2zCDop96Ahdg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Hash partitioning. (Bruce Momjian <bruce@momjian.us>) |
Ответы |
Re: Hash partitioning.
Re: Hash partitioning. |
Список | pgsql-hackers |
On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian <bruce@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 <bruce@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 onfoo (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) -> SeqScan on foo2 (cost=0.00..25.38 rows=6 width=36) Filter: (a = 1) -> Seq Scan on foo3 (cost=0.00..25.38 rows=6width=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
В списке pgsql-hackers по дате отправления: