Skip site navigation (1) Skip section navigation (2)

different behaviour between select and delete when constraint_exclusion = partition

From: Marc Cousin <cousinmarc(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: different behaviour between select and delete when constraint_exclusion = partition
Date: 2010-03-30 12:22:25
Message-ID: 201003301422.25673.cousinmarc@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: moataz ElmasryDate: 2010-03-30 13:55:34
Subject: createuser: could not connect to database postgres: FATAL: password authentication failed for user "postgres"
Previous:From: A. KretschmerDate: 2010-03-30 11:26:29
Subject: Re: Running/cumulative count using windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group