Partitionin with check functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux