Thread: Partitionin with check functions
| Hi My problem in partitioning is about using functions in table check constraints. let me explain it " DROP DATABASE partitioning; \set ON_ERROR_STOP y CREATE DATABASE partitioning; ALTER DATABASE partitioning set constraint_exclusion TO true; \c partitioning CREATE language plpgsql; CREATE TABLE mainlog (sel int); CREATE TABLE mainlog_p0 (CHECK (mod(sel,6)=0)) INHERITS (mainlog); CREATE TABLE mainlog_p1 (CHECK (mod(sel,6)=1)) INHERITS (mainlog); CREATE TABLE mainlog_p2 (CHECK (mod(sel,6)=2)) INHERITS (mainlog); CREATE TABLE mainlog_p3 (CHECK (mod(sel,6)=3)) INHERITS (mainlog); CREATE TABLE mainlog_p4 (CHECK (mod(sel,6)=4)) INHERITS (mainlog); CREATE TABLE mainlog_p5 (CHECK (mod(sel,6)=5)) INHERITS (mainlog); CREATE OR REPLACE FUNCTION trg_mainlog_partitioner() RETURNS TRIGGER AS $$ BEGIN IF mod(NEW.sel,6) = 0 THEN INSERT INTO mainlog_p0 VALUES (NEW.*); ELSIF mod(NEW.sel,6) = 1 THEN INSERT INTO mainlog_p1 VALUES (NEW.*); ELSIF mod(NEW.sel,6) = 2 THEN INSERT INTO mainlog_p2 VALUES (NEW.*); ELSIF mod(NEW.sel,6) = 3 THEN INSERT INTO mainlog_p3 VALUES (NEW.*); ELSIF mod(NEW.sel,6) = 4 THEN INSERT INTO mainlog_p4 VALUES (NEW.*); ELSIF mod(NEW.sel,6) = 5 THEN INSERT INTO mainlog_p5 VALUES (NEW.*); END IF; RETURN NULL; END; $$ language plpgsql; CREATE TRIGGER trg_mainlog_partitioner BEFORE INSERT ON mainlog FOR EACH ROW execute procedure trg_mainlog_partitioner(); INSERT INTO mainlog(sel) SELECT * FROM generate_series(1,1000); " partitioning=# SELECT * from only mainlog; sel ----- (0 rows) partitioning=# SELECT * from only mainlog_p1; sel ----- 1 7 13 19 25 31 ..... it seems tables contains expected datas. But lets explain queries partitioning=# explain SELECT * from mainlog where sel=123; QUERY PLAN ------------------------------------------------------------------------------ Result (cost=0.00..58.50 rows=18 width=4) -> Append (cost=0.00..58.50 rows=18 width=4) -> Seq Scan on mainlog (cost=0.00..40.00 rows=12 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p0 mainlog (cost=0.00..3.08 rows=1 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p1 mainlog (cost=0.00..3.09 rows=1 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p2 mainlog (cost=0.00..3.09 rows=1 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p3 mainlog (cost=0.00..3.09 rows=1 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p4 mainlog (cost=0.00..3.09 rows=1 width=4) Filter: (sel = 123) -> Seq Scan on mainlog_p5 mainlog (cost=0.00..3.08 rows=1 width=4) Filter: (sel = 123) (16 rows) i am expecting that query planer only scans "mainlog_p3" not the others since mod(123,6)=3 so 123 can only be in "mainlog_p3" table and scaning other tables is waste of time. What is the problem with my partitioning strategy? How can i make it fixed and make it working as i expected? Any suggestions Thanks for your interest Note: Forgive me, i can not give real table definitions. |
fatih ozturk <ozturkfa@yahoo.com> writes:
> My problem in partitioning is about using functions in table check constraints.
By and large, you can't. Per the fine manual:
Keep the partitioning constraints simple, else the planner may not be
able to prove that partitions don't need to be visited. Use simple
equality conditions for list partitioning, or simple range tests for
range partitioning, as illustrated in the preceding examples. A good
rule of thumb is that partitioning constraints should contain only
comparisons of the partitioning column(s) to constants using
B-tree-indexable operators.
In particular, a constraint like "sel=123" is *not* going to lead the
planner to draw any conclusions about the value of "mod(sel,6)".
Now, if you'd written "WHERE mod(sel,6)=3", I think it would draw
the right conclusions.
The underlying issue here is that "a=b" does not imply "f(a)=f(b)"
for all functions f, not even if "=" is known to be a btree equality
operator. Several of PG's standard datatypes have counterexamples,
so the planner *must* take this seriously.
regards, tom lane
In article <2499.1238420859@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> writes: > In particular, a constraint like "sel=123" is *not* going to lead the > planner to draw any conclusions about the value of "mod(sel,6)". > Now, if you'd written "WHERE mod(sel,6)=3", I think it would draw > the right conclusions. Yes, it does. It might be worthwile to use WHERE sel=123 AND mod(sel,6)=3 I think you could use a rule to add the mod() condition automatically.
| Thank you "Yes, it does. It might be worthwile to use WHERE sel=123 AND mod(sel,6)=3 I think you could use a rule to add the mod() condition automatically. " this is great idea But the problem is i did not accomplish to find a way to change query like partitioning=# select * from mainlog where sel=22; to partitioning=# select * from mainlog where sel=22 and mod(sel,6)=4; "on select" rules just creates views, dont routes the queries. I cant use tirggers for selects. Is there exists a way of changing select query before query plan generated? Any ideas? I am very appreciative for your answers and interest. Thanks --- On Mon, 3/30/09, Harald Fuchs <hari.fuchs@gmail.com> wrote:
|