Thread: different behaviour between select and delete when constraint_exclusion = partition
different behaviour between select and delete when constraint_exclusion = partition
From
Marc Cousin
Date:
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
Re: different behaviour between select and delete when constraint_exclusion = partition
From
Tom Lane
Date:
Marc Cousin <cousinmarc@gmail.com> writes:
> 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
Hmm, this seems like a shortcoming in the constraint_exclusion=partition
feature. The reason it doesn't work is that inheritance expansion of a
DELETE/UPDATE target rel is handled entirely differently from expansion
of a SELECT source. But it seems like it would be desirable if it did
work.
regards, tom lane