From: | fatih ozturk <ozturkfa(at)yahoo(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Partitionin with check functions |
Date: | 2009-03-30 08:34:52 |
Message-ID: | 20867.32524.qm@web111513.mail.gq1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-03-30 13:47:39 | Re: Partitionin with check functions |
Previous Message | Tom Lane | 2009-03-30 02:45:01 | Re: forgot the structure of a composite type |