Re: Hash partitioning.

Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:

> Did you try "select * from foo where (a % 16) = (1::int % 16)"?

I did.  Using Robert's hashed partitioning table definitions:

test=# explain select * from foo where a = 1 and (a % 16) = (1 % 16);
                         QUERY PLAN                        
------------------------------------------------------------
 Append  (cost=0.00..31.53 rows=2 width=36)
   ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=36)
         Filter: ((a = 1) AND ((a % 16) = 1))
   ->  Seq Scan on foo1  (cost=0.00..31.53 rows=1 width=36)
         Filter: ((a = 1) AND ((a % 16) = 1))
(5 rows)

So if you are generating your queries through something capable of
generating that last clause off of the first, this could work.  Not
all applications need to remain as flexible about the operators as
we want the database engine itself to be.

I agree though, that having an index implementation that can do the
first level split faster than any partitioning mechanism can do is
better, and that the main benefits of partitioning are in
administration, *not* searching.  At least until we have parallel
query execution.  At *that* point this all changes.

One other thing worth noting is that I have several times seen
cases where the planner cannot exclude partitions, but at execution
time it finds that it doesn't need to execute all of the plan
nodes.  I think it makes sense to not work quite so hard to
eliminate partitions at plan time if we can skip the unneeded ones
at run time, once we have more data values resolved.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company