From: fatih ozturk <ozturkfa@yahoo.com>
To: pgsql-admin@postgresql.org
Subject: Partitionin with check functions
Date: Mon, 30 Mar 2009 01:34:52 -0700 (PDT)
Message-ID: <20867.32524.qm@web111513.mail.gq1.yahoo.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ozturkfa@yahoo.com
Subject: Re: Partitionin with check functions
In-Reply-To: <20867.32524.qm@web111513.mail.gq1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox