On Tue, Jun 25, 2013 at 12:55 PM, Robert Haas <robertmh...@gmail.com> wrote: > Let me back up a minute. You told the OP that he could make hash > partitioning by writing his own constraint and trigger functions. I > think that won't work. But I'm happy to be proven wrong. Do you have > an example showing how to do it? > > Here's why I think it WON'T work: > > rhaas=# create table foo (a int, b text); > CREATE TABLE > rhaas=# create table foo0 (check ((a % 16) = 0)) inherits (foo); > CREATE TABLE > rhaas=# create table foo1 (check ((a % 16) = 1)) inherits (foo); > CREATE TABLE > rhaas=# create table foo2 (check ((a % 16) = 2)) inherits (foo); > CREATE TABLE > rhaas=# create table foo3 (check ((a % 16) = 3)) inherits (foo); > CREATE TABLE > rhaas=# explain select * from foo where a = 1; > QUERY PLAN > ------------------------------------------------------------ > Append (cost=0.00..101.50 rows=25 width=36) > -> Seq Scan on foo (cost=0.00..0.00 rows=1 width=36) > Filter: (a = 1) > -> Seq Scan on foo0 (cost=0.00..25.38 rows=6 width=36) > Filter: (a = 1) > -> Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36) > Filter: (a = 1) > -> Seq Scan on foo2 (cost=0.00..25.38 rows=6 width=36) > Filter: (a = 1) > -> Seq Scan on foo3 (cost=0.00..25.38 rows=6 width=36) > Filter: (a = 1) > (11 rows) > > Notice we get a scan on every partition. Now let's try it with no > modulo arithmetic, just a straightforward one-partition-per-value: > > rhaas=# create table foo (a int, b text); > CREATE TABLE > rhaas=# create table foo0 (check (a = 0)) inherits (foo); > CREATE TABLE > rhaas=# create table foo1 (check (a = 1)) inherits (foo); > CREATE TABLE > rhaas=# create table foo2 (check (a = 2)) inherits (foo); > CREATE TABLE > rhaas=# create table foo3 (check (a = 3)) inherits (foo); > CREATE TABLE > rhaas=# explain select * from foo where a = 1;
Did you try "select * from foo where (a % 16) = (1::int % 16)"? A few views I have that span multiple "partitions" (in quotes since they're not exactly partitions, but close), I can make constraint exclusion work if I match the expression EXACTLY, including types (I've posted a few questions about this to pg-performance). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers