Re: Hash partitioning.

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