Re: Hash partitioning.

On Tue, Jun 25, 2013 at 12:55 PM, Robert Haas <robertmhaas(at)gmail(dot)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).