From: Martin Lesser <ml-pgsql@bettercom.de>
To: pgsql-performance@postgresql.org
Subject: Re: Partitioning / constrain exlusion not working with %-operator
In-Reply-To: <13552.1154349777@sss.pgh.pa.us> (Tom Lane's message of "Mon, 31
	Jul 2006 08:42:57 -0400")
References: <87ejw27yuz.fsf@fs-home.bettercom.de>
	<13552.1154349777@sss.pgh.pa.us>
Date: Fri, 04 Aug 2006 09:36:56 +0200
Message-ID: <8764h93qav.fsf@fs-home.bettercom.de>
User-Agent: Gnus/5.110004 (No Gnus v0.4) XEmacs/21.4.18 (linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Tom Lane <tgl@sss.pgh.pa.us> writes:

> It's usually better to use partitioning rules that have something to
> do with the WHERE-clauses you'd be using anyway.  For instance, try
> to partition on ranges.

I agree and tried to create new partitioned tables. But now I ran into
some other performance-related trouble when inserting (parts of) the old
(unpartioned) table into the new one:

CREATE TABLE t_unparted (id1 int, cont varchar);
-- Populate table with 1000 records with id1 from 1 to 1000 and ANALYZE

CREATE TABLE t_parted (id1 int, cont varchar);
CREATE TABLE t_parted_000 (check (id1 >=0 AND id1 < 100)) INHERITS (t_parted);
CREATE RULE ins_000 AS ON INSERT TO t_parted  WHERE id1 >= 0 AND id1 < 100 DO INSTEAD INSERT INTO t_parted_000 VALUES (new.*);
-- ... 8 more tables + 8 more rules
CREATE TABLE t_parted_900 (check (id1 >=900 AND id1 < 1000)) INHERITS (t_parted);
CREATE RULE ins_900 AS ON INSERT TO t_parted  WHERE id1 >= 900 AND id1 < 1000 DO INSTEAD INSERT INTO t_parted_900 VALUES (new.*);

And now:

EXPLAIN INSERT INTO t_parted SELECT * FROM t_parted WHERE id1>=0 AND id1<100;

 Result  (cost=0.00..170.80 rows=12 width=36)
   ->  Append  (cost=0.00..170.80 rows=12 width=36)
         ->  Seq Scan on t_parted  (cost=0.00..85.40 rows=6 width=36)
               Filter: ((id1 >= 0) AND (id1 < 100) AND (((id1 >= 0) AND (id1 < 100)) IS NOT TRUE) AND (((id1 >= 100) AND (id1 < 200)) IS NOT TRUE) AND (((id1 >= 200) AND (id1 < 300)) IS NOT TRUE) AND (((id1 >= 300) AND (id1 < 400)) IS NOT TRUE) AND (((id1 >= 400) AND (id1 < 500)) IS NOT TRUE) AND (((id1 >= 500) AND (id1 < 600)) IS NOT TRUE) AND (((id1 >= 600) AND (id1 < 700)) IS NOT TRUE) AND (((id1 >= 700) AND (id1 < 800)) IS NOT TRUE) AND (((id1 >= 800) AND (id1 < 900)) IS NOT TRUE) AND (((id1 >= 900) AND (id1 < 1000)) IS NOT TRUE))
         ->  Seq Scan on t_parted_000 t_parted  (cost=0.00..85.40 rows=6 width=36)
               Filter: ((id1 >= 0) AND (id1 < 100) AND (((id1 >= 0) AND (id1 < 100)) IS NOT TRUE) AND (((id1 >= 100) AND (id1 < 200)) IS NOT TRUE) AND (((id1 >= 200) AND (id1 < 300)) IS NOT TRUE) AND (((id1 >= 300) AND (id1 < 400)) IS NOT TRUE) AND (((id1 >= 400) AND (id1 < 500)) IS NOT TRUE) AND (((id1 >= 500) AND (id1 < 600)) IS NOT TRUE) AND (((id1 >= 600) AND (id1 < 700)) IS NOT TRUE) AND (((id1 >= 700) AND (id1 < 800)) IS NOT TRUE) AND (((id1 >= 800) AND (id1 < 900)) IS NOT TRUE) AND (((id1 >= 900) AND (id1 < 1000)) IS NOT TRUE))

 Result  (cost=0.00..66.40 rows=12 width=36)
   ->  Append  (cost=0.00..66.40 rows=12 width=36)
         ->  Seq Scan on t_parted  (cost=0.00..33.20 rows=6 width=36)
               Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 0) AND (id1 < 100))
         ->  Seq Scan on t_parted_000 t_parted  (cost=0.00..33.20 rows=6 width=36)
               Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 0) AND (id1 < 100))
         ...
 Result  (cost=0.00..33.20 rows=6 width=36)
   ->  Append  (cost=0.00..33.20 rows=6 width=36)
         ->  Seq Scan on t_parted  (cost=0.00..33.20 rows=6 width=36)
               Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 900) AND (id1 < 1000))
(58 rows)

The filters appended by the planner do not make any sense and cost too
much time if the old table is huge. (constraint_exclusion was ON)

Is there a better way to partition an existing table with a large
number of rows (>100 mio)?

TIA, Martin