From: Martin Lesser <ml-pgsql@bettercom.de>
To: pgsql-performance@postgresql.org
Subject: Partitioning / constrain exlusion not working with %-operator
Date: Mon, 31 Jul 2006 14:17:08 +0200
Message-ID: <87ejw27yuz.fsf@fs-home.bettercom.de> (raw)
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
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-performance@postgresql.org
Cc: ml-pgsql@bettercom.de
Subject: Re: Partitioning / constrain exlusion not working with %-operator
In-Reply-To: <87ejw27yuz.fsf@fs-home.bettercom.de>
* 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