Lists: | pgsql-performance |
---|
From: | Martin Lesser <ml-pgsql(at)bettercom(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Partitioning / constrain exlusion not working with %-operator |
Date: | 2006-07-31 12:17:08 |
Message-ID: | 87ejw27yuz.fsf@fs-home.bettercom.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
I try to partition a large table (~ 120 mio. rows) into 50 smaller
tables but using the IMO immutable %-function constraint exclusion
does not work as expected:
CREATE TABLE tt_m (id1 int, cont varchar);
CREATE TABLE tt_0 (check (id1 % 50 = 0)) INHERITS (tt_m);
CREATE TABLE tt_1 (check (id1 % 50 = 1)) INHERITS (tt_m);
....
CREATE RULE ins_tt_0 AS ON INSERT TO tt_m WHERE id1 % 50 = 0 DO INSTEAD INSERT INTO tt_0 VALUES (new.*);
CREATE RULE ins_tt_1 AS ON INSERT TO tt_m WHERE id1 % 50 = 1 DO INSTEAD INSERT INTO tt_1 VALUES (new.*);
...
INSERT INTO tt_m (id1,cont) VALUES (0,'Test1');
INSERT INTO tt_m (id1,cont) VALUES (1,'Test2');
....
EXPLAIN SELECT * FROM tt_m WHERE id1=1;
QUERY PLAN
-----------------------------------------------------------------------
Result (cost=0.00..73.50 rows=18 width=36)
-> Append (cost=0.00..73.50 rows=18 width=36)
-> Seq Scan on tt_m (cost=0.00..24.50 rows=6 width=36)
Filter: (id1 = 1)
-> Seq Scan on tt_0 tt_m (cost=0.00..24.50 rows=6 width=36)
Filter: (id1 = 1)
-> Seq Scan on tt_1 tt_m (cost=0.00..24.50 rows=6 width=36)
Filter: (id1 = 1)
...
Only adding an explicit %-call to the query results in the expected plan:
EXPLAIN SELECT * FROM tt_m WHERE id1=1 AND id1 % 50 = 1;
QUERY PLAN
-----------------------------------------------------------------------
Result (cost=0.00..60.60 rows=2 width=36)
-> Append (cost=0.00..60.60 rows=2 width=36)
-> Seq Scan on tt_m (cost=0.00..30.30 rows=1 width=36)
Filter: ((id1 = 1) AND ((id1 % 50) = 1))
-> Seq Scan on tt_1 tt_m (cost=0.00..30.30 rows=1 width=36)
Filter: ((id1 = 1) AND ((id1 % 50) = 1))
Did I miss something and/or how could I force the planner to use
constraint exclusion without adding the explicit second condition above?
TIA, Martin
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Martin Lesser <ml-pgsql(at)bettercom(dot)de> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Partitioning / constrain exlusion not working with %-operator |
Date: | 2006-07-31 12:42:57 |
Message-ID: | 13552.1154349777@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Martin Lesser <ml-pgsql(at)bettercom(dot)de> writes:
> I try to partition a large table (~ 120 mio. rows) into 50 smaller
> tables but using the IMO immutable %-function constraint exclusion
> does not work as expected:
The constraint exclusion mechanism is not as bright as you think.
There are some very limited cases where it can make a deduction that
a WHERE clause implies a CHECK constraint that's not an exact textual
equivalent ... but all those cases have to do with related b-tree
operators, and % is not one.
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 of id1 instead of id1 % 50. That works because
the CHECK clauses will be like "id1 >= x and id1 < y" and those
operators are btree-related to the "id1 = z" clauses you'll have in
the query.
regards, tom lane
From: | Martin Lesser <ml-pgsql(at)bettercom(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Partitioning / constrain exlusion not working with %-operator |
Date: | 2006-08-04 07:36:56 |
Message-ID: | 8764h93qav.fsf@fs-home.bettercom.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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