Hi, I don't know if what's below is a bug or simply not implemented. And I
don't really know if, when in doubt, like now, I'd rather pollute general or
bugs :)
Anyway here is the problem : when using constraint_exclusion=partition, a
delete query scans all partitions, when the same query rewritten as a select
is removing partitions as expected. When constraint_exclusion=on, the
partition removal works as expected with the delete too. I've tested it on 8.4
and 9.0
Anyway here is the test case to demonstrate this :
CREATE TABLE test (
a integer
);
CREATE TABLE a1 (CONSTRAINT a1_a_check CHECK ((a = 1))
)
INHERITS (test);
CREATE TABLE a2 (CONSTRAINT a2_a_check CHECK ((a = 2))
)
INHERITS (test);
CREATE TABLE a3 (CONSTRAINT a3_a_check CHECK ((a = 3))
)
INHERITS (test);
Everything is empty…
marc=# SHOW constraint_exclusion ;
constraint_exclusion
----------------------
partition
(1 row)
This works :
marc=# EXPLAIN SELECT * FROM test WHERE a=1;
QUERY PLAN
---------------------------------------------------------------------
Result (cost=0.00..80.00 rows=24 width=4)
-> Append (cost=0.00..80.00 rows=24 width=4)
-> Seq Scan on test (cost=0.00..40.00 rows=12 width=4)
Filter: (a = 1)
-> Seq Scan on a1 test (cost=0.00..40.00 rows=12 width=4)
Filter: (a = 1)
(6 rows)
This doesn't :
marc=# EXPLAIN DELETE FROM test WHERE a=1;
QUERY PLAN
---------------------------------------------------------------
Delete (cost=0.00..160.00 rows=48 width=6)
-> Seq Scan on test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
-> Seq Scan on a1 test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
-> Seq Scan on a2 test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
-> Seq Scan on a3 test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
(9 rows)
When putting constraint_exclusion to on :
marc=# SET constraint_exclusion TO on;
SET
marc=# EXPLAIN DELETE FROM test WHERE a=1;
QUERY PLAN
---------------------------------------------------------------
Delete (cost=0.00..80.00 rows=24 width=6)
-> Seq Scan on test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
-> Seq Scan on a1 test (cost=0.00..40.00 rows=12 width=6)
Filter: (a = 1)
(5 rows)
Still, I don't know if this qualifies as a bug.
Cheers,
Marc
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
