Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 626BA633654 for ; Mon, 30 Mar 2009 05:42:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10651-02 for ; Mon, 30 Mar 2009 05:42:41 -0300 (ADT) X-Greylist: delayed 00:06:40.960011 by SQLgrey-1.7.6 Received: from web111513.mail.gq1.yahoo.com (web111513.mail.gq1.yahoo.com [67.195.15.205]) by mail.postgresql.org (Postfix) with SMTP id 6398C634085 for ; Mon, 30 Mar 2009 05:41:36 -0300 (ADT) Received: (qmail 32786 invoked by uid 60001); 30 Mar 2009 08:34:53 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1238402093; bh=QnX9+YKhyj7YoViBFPKfQgC5TziGyvACzSCSsGWB778=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=yNVQMgHnIsxM2AyOIwSaJ364wcwQaypbFafNCqDycdTobcq//lNhXejR8+I149ZeFtCPc9RqmhD/aqiMTCEVoSFm2mcDJZvu4ORLqI+v4NUiN/JJAW1mOBQtxLEaZf8ftsHNtL6iWn2uXM87J4HXZx1WH7LY7oJNUfiedCWsvIA= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=skXNvdXa/5JgjSx25HOLw4+Kwa7+zE9evyvw44fNprmiyNhvaOBZ3vRv8MqTyB2ajxBIlczV0ttjuMDtCCsxoNSH2Ww5OvFUQrSHASbZ3KtPPvPeEXv6oKtlOTo16/wYaZUdE99jIwGbLF0jmgIcunz8eDhV+LE+fLrTgN9wc5w=; Message-ID: <20867.32524.qm@web111513.mail.gq1.yahoo.com> X-YMail-OSG: 4a2GD7YVM1lJj7MFKngVDJVZ0mBMIJ4C07hopv6C_lZtp4zCaI.zcbVZA6iY1aw8O9TtbrAnVvNGWeF5EKyDczlrP7Ql6JdE32YwC.ITie.5W3u2UIkXxhEaQJr07X5EkeUM2osQ6V3ZwA6seYalGaih.s4NWH0pPVdNFfZ2yWVtBsPSpk3KcrqlvjAR Received: from [195.175.16.46] by web111513.mail.gq1.yahoo.com via HTTP; Mon, 30 Mar 2009 01:34:52 PDT X-Mailer: YahooMailClassic/5.2.14 YahooMailWebService/0.7.289.1 Date: Mon, 30 Mar 2009 01:34:52 -0700 (PDT) From: fatih ozturk Subject: Partitionin with check functions To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-683321127-1238402092=:32524" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.411 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200903/246 X-Sequence-Number: 31746 --0-683321127-1238402092=:32524 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi My problem in partitioning is about using functions in table check constrai= nts. 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)=3D0)) INHERITS (mainlog); CREATE TABLE mainlog_p1 (CHECK (mod(sel,6)=3D1)) INHERITS (mainlog); CREATE TABLE mainlog_p2 (CHECK (mod(sel,6)=3D2)) INHERITS (mainlog); CREATE TABLE mainlog_p3 (CHECK (mod(sel,6)=3D3)) INHERITS (mainlog); CREATE TABLE mainlog_p4 (CHECK (mod(sel,6)=3D4)) INHERITS (mainlog); CREATE TABLE mainlog_p5 (CHECK (mod(sel,6)=3D5)) INHERITS (mainlog); CREATE OR REPLACE FUNCTION trg_mainlog_partitioner() RETURNS TRIGGER AS $$ BEGIN=20 =A0=A0=A0 IF mod(NEW.sel,6) =3D 0 THEN INSERT INTO mainlog_p0 VALUES (NEW.*= ); =A0=A0=A0 ELSIF mod(NEW.sel,6) =3D 1 THEN INSERT INTO mainlog_p1 VALUES (NE= W.*); =A0=A0=A0 ELSIF mod(NEW.sel,6) =3D 2 THEN INSERT INTO mainlog_p2 VALUES (NE= W.*); =A0=A0=A0 ELSIF mod(NEW.sel,6) =3D 3 THEN INSERT INTO mainlog_p3 VALUES (NE= W.*); =A0=A0=A0 ELSIF mod(NEW.sel,6) =3D 4 THEN INSERT INTO mainlog_p4 VALUES (NE= W.*); =A0=A0=A0 ELSIF mod(NEW.sel,6) =3D 5 THEN INSERT INTO mainlog_p5 VALUES (NE= W.*); =A0=A0=A0 END IF; =A0=A0=A0 RETURN NULL; END; $$ language plpgsql; CREATE TRIGGER trg_mainlog_partitioner BEFORE INSERT ON mainlog FOR EACH RO= W execute procedure trg_mainlog_partitioner(); INSERT INTO mainlog(sel) SELECT * FROM generate_series(1,1000); " partitioning=3D# SELECT * from only mainlog; =A0sel=20 ----- (0 rows) partitioning=3D# SELECT * from only mainlog_p1; =A0sel=20 ----- =A0=A0 1 =A0=A0 7 =A0 13 =A0 19 =A0 25 =A0 31 ..... it seems tables contains expected datas. But lets explain queries partitioning=3D# explain SELECT * from mainlog where sel=3D123; =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 ---------------------------------------------------------------------------= --- =A0Result=A0 (cost=3D0.00..58.50 rows=3D18 width=3D4) =A0=A0 ->=A0 Append=A0 (cost=3D0.00..58.50 rows=3D18 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog=A0 (cost=3D0.00..40.00 r= ows=3D12 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p0 mainlog=A0 (cost=3D0.= 00..3.08 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p1 mainlog=A0 (cost=3D0.= 00..3.09 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p2 mainlog=A0 (cost=3D0.= 00..3.09 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p3 mainlog=A0 (cost=3D0.= 00..3.09 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p4 mainlog=A0 (cost=3D0.= 00..3.09 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Seq Scan on mainlog_p5 mainlog=A0 (cost=3D0.= 00..3.08 rows=3D1 width=3D4) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: (sel =3D 123) (16 rows) i am expecting that query planer only scans "mainlog_p3" not the others sin= ce mod(123,6)=3D3 so 123 can only be in "mainlog_p3" table and scaning othe= r 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. =0A=0A=0A --0-683321127-1238402092=:32524 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
Hi

My problem in partitioning is about= using functions in table check constraints.
let me explain it

"<= br>
DROP DATABASE partitioning;
\set ON_ERROR_STOP y
CREATE DATABA= SE partitioning;
ALTER DATABASE partitioning set constraint_exclusion TO= true;
\c partitioning
CREATE language plpgsql;
CREATE TABLE mainl= og (sel int);
CREATE TABLE mainlog_p0 (CHECK (mod(sel,6)=3D0)) INHERITS = (mainlog);
CREATE TABLE mainlog_p1 (CHECK (mod(sel,6)=3D1)) INHERITS (ma= inlog);
CREATE TABLE mainlog_p2 (CHECK (mod(sel,6)=3D2)) INHERITS (mainl= og);
CREATE TABLE mainlog_p3 (CHECK (mod(sel,6)=3D3)) INHERITS (mainlog)= ;
CREATE TABLE mainlog_p4 (CHECK (mod(sel,6)=3D4)) INHERITS (mainlog);CREATE TABLE mainlog_p5 (CHECK (mod(sel,6)=3D5)) INHERITS (mainlog);
<= br>CREATE OR REPLACE FUNCTION trg_mainlog_partitioner() RETURNS TRIGGER AS = $$
BEGIN
    IF mod(NEW.sel,6) =3D 0 THEN INSERT INTO mainlog_p0= VALUES (NEW.*);
    ELSIF mod(NEW.sel,6) =3D 1 THEN INSE= RT INTO mainlog_p1 VALUES (NEW.*);
    ELSIF mod(NEW.sel,= 6) =3D 2 THEN INSERT INTO mainlog_p2 VALUES (NEW.*);
    = ELSIF mod(NEW.sel,6) =3D 3 THEN INSERT INTO mainlog_p3 VALUES (NEW.*);
&= nbsp;   ELSIF mod(NEW.sel,6) =3D 4 THEN INSERT INTO mainlog_p4 VA= LUES (NEW.*);
    ELSIF mod(NEW.sel,6) =3D 5 THEN INSERT = INTO mainlog_p5 VALUES (NEW.*);
    END IF;
 &nbs= p;  RETURN NULL;
END;
$$ language plpgsql;

CREATE TRIGGER= trg_mainlog_partitioner BEFORE INSERT ON mainlog FOR EACH ROW execute proc= edure trg_mainlog_partitioner();

INSERT INTO mainlog(sel) SELECT * F= ROM generate_series(1,1000);

"
partitioning=3D# SELECT * from onl= y mainlog;
 sel
-----
(0 rows)

partitioning=3D# SELEC= T * from only mainlog_p1;
 sel
-----
   1
   = 7
  13
  19
  25
  31
.....

it se= ems tables contains expected datas.

But lets explain queries

= partitioning=3D# explain SELECT * from mainlog where sel=3D123;
 &n= bsp;            = ;            &n= bsp;       QUERY PLAN    =             &nb= sp;            =     
----------------------------------------------= --------------------------------
 Result  (cost=3D0.00..58.50 = rows=3D18 width=3D4)
   ->  Append  (cost=3D0.00.= .58.50 rows=3D18 width=3D4)
       &n= bsp; ->  Seq Scan on mainlog  (cost=3D0.00..40.00 rows=3D12 width= =3D4)
           =     Filter: (sel =3D 123)
     &= nbsp;   ->  Seq Scan on mainlog_p0 mainlog  (cost=3D= 0.00..3.08 rows=3D1 width=3D4)
       = ;        Filter: (sel =3D 123)
 =         ->  Seq Scan on mainlog_= p1 mainlog  (cost=3D0.00..3.09 rows=3D1 width=3D4)
  &nbs= p;            Filter= : (sel =3D 123)
         ->&n= bsp; Seq Scan on mainlog_p2 mainlog  (cost=3D0.00..3.09 rows=3D1 width= =3D4)
           =     Filter: (sel =3D 123)
     &= nbsp;   ->  Seq Scan on mainlog_p3 mainlog  (cost=3D0.00..3.09 rows= =3D1 width=3D4)
         &n= bsp;     Filter: (sel =3D 123)
   &nb= sp;     ->  Seq Scan on mainlog_p4 mainlog = ; (cost=3D0.00..3.09 rows=3D1 width=3D4)
     &= nbsp;         Filter: (sel =3D 123)=
         ->  Seq Scan o= n mainlog_p5 mainlog  (cost=3D0.00..3.08 rows=3D1 width=3D4)
 =             &nb= sp; Filter: (sel =3D 123)
(16 rows)

i am expecting that query pla= ner only scans "mainlog_p3" not the others since mod(123,6)=3D3 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 i= t fixed and make it working as i expected?
Any suggestions

Thanks for your interest<= br>
Note: Forgive me, i can not give real table definitions.

=0A=0A=0A=0A --0-683321127-1238402092=:32524--