Alex- http://www.postgresql.org/docs/8.2/static/runtime-config-query.html#GUC-CONS TRAINT-EXCLUSION postgresql.conf contains a constraint_exclusion parameter called constraint_exclusion (boolean) which if you dont want to scan ALL partitions must be set to 'on' constraint_exclusion = on HTH Martin ----- Original Message ----- From: "Alex Solovey" <a.solovey@xxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> Sent: Monday, March 24, 2008 2:15 PM Subject: Problem with update on partitioned table > Hello, > > We have pretty big production database (running PostgreSQL 8.3.1) with > many partitioned tables. In most cases, they work well (since 8.2.1 at > least) -- constraint exclusion is able to select correct partitions. > However, there is an exception: queries on partitioned tables using > PostgreSQL 'UPDATE Foo ... FROM Bar' syntax extension. > > Here is a simple test case: > ------------------ > CREATE TABLE bar ( bar_id INT NOT NULL PRIMARY KEY ); > INSERT INTO bar VALUES ( 1 ), ( 2 ), ( 3 ); > > CREATE TABLE foo ( > part INT NOT NULL > ,foo_data INT > ,bar_id INT NOT NULL REFERENCES bar( bar_id ) > ); > > CREATE TABLE foo_1 ( CHECK ( part = 1 ) ) INHERITS ( foo ); > INSERT INTO foo_1 ( part, bar_id ) VALUES ( 1, 1 ), ( 1, 3 ); > > CREATE TABLE foo_2 ( CHECK ( part = 2 ) ) INHERITS ( foo ); > INSERT INTO foo_2 ( part, bar_id ) VALUES ( 2, 2 ), ( 2, 3 ); > > CREATE TABLE foo_3 ( CHECK ( part = 3 ) ) INHERITS ( foo ); > INSERT INTO foo_3 ( part, bar_id ) VALUES ( 3, 1 ), ( 3, 2 ); > ------------------ > > As you can see, table "Foo" is partitioned by column "part". If only > "Foo" is referenced in update, query plan is fine: > > => EXPLAIN UPDATE foo SET foo_data = 10 WHERE part = 2; > QUERY PLAN > ------------------------------------------------------------------ > Append (cost=0.00..68.50 rows=20 width=14) > -> Seq Scan on foo (cost=0.00..34.25 rows=10 width=14) > Filter: (part = 2) > -> Seq Scan on foo_2 foo (cost=0.00..34.25 rows=10 width=14) > Filter: (part = 2) > (5 rows) > > However, for this query it is far from being optimal: > > => EXPLAIN UPDATE foo SET foo_data = 10 FROM bar WHERE part = 2 AND > foo.bar_id = bar.bar_id; > QUERY PLAN > > -------------------------------------------------------------------------- ------ > Append (cost=0.00..nan rows=22 width=14) > -> Nested Loop (cost=0.00..73.05 rows=10 width=14) > -> Seq Scan on foo (cost=0.00..34.25 rows=10 width=14) > Filter: (part = 2) > -> Index Scan using bar_pkey on bar (cost=0.00..3.87 rows=1 > width=4) > Index Cond: (bar.bar_id = public.foo.bar_id) > -> Merge Join (cost=nan..nan rows=1 width=8) > Merge Cond: (public.foo.bar_id = bar.bar_id) > -> Sort (cost=0.02..0.03 rows=1 width=0) > Sort Key: public.foo.bar_id > -> Result (cost=0.00..0.01 rows=1 width=0) > One-Time Filter: false > -> Sort (cost=168.75..174.75 rows=2400 width=4) > Sort Key: bar.bar_id > -> Seq Scan on bar (cost=0.00..34.00 rows=2400 width=4) > -> Nested Loop (cost=0.00..73.05 rows=10 width=14) > -> Seq Scan on foo_2 foo (cost=0.00..34.25 rows=10 width=14) > Filter: (part = 2) > -> Index Scan using bar_pkey on bar (cost=0.00..3.87 rows=1 > width=4) > Index Cond: (bar.bar_id = public.foo.bar_id) > -> Merge Join (cost=nan..nan rows=1 width=8) > Merge Cond: (public.foo.bar_id = bar.bar_id) > -> Sort (cost=0.02..0.03 rows=1 width=0) > Sort Key: public.foo.bar_id > -> Result (cost=0.00..0.01 rows=1 width=0) > One-Time Filter: false > -> Sort (cost=168.75..174.75 rows=2400 width=4) > Sort Key: bar.bar_id > -> Seq Scan on bar (cost=0.00..34.00 rows=2400 width=4) > (29 rows) > > Is there any way to avoid this anomaly? UPDATE ... FROM is very > convenient if you have to update rows depending on conditions involving > multiple tables. In addition, with partitioned tables, > standard-conforming UPDATE foo ... WHERE pk IN (SELECT pk FROM foo, > bar...) is even worse because query planner cannot choose correct > partitions without nested select results and so it does a complete scan > of all partitions instead. > > Alex > > - > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > - Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
