Question on round-robin partitioning - Mailing list pgsql-general

Purely for performance, I was looking into partitioning
some tables round-robin by value. Question is whether
there is any way to make use of this in constraint
exclusion.

Say I have a table foo with serial variable "foo_id".
The partition checks are

    foo_id % 8 = 0
    foo_id % 8 = 1
    foo_id % 8 = 2
    ...

If I query on foo_id % 8, explain shows the optimizer
using the constraint (1).

If I just query on foo_id = 100, the exclusion is
not used (2).

What would be the best way to feed the optimizer
enough data to use the partitioning with equality
queries?

I've come up with adding a field in the various
tables to store the id % 8 values and adding
"and a.mod8_field = b.mod8_field" but hopefully
there is a better way.

That or it might be a useful addition to some
later version to handle "serial field % N" in
the optimizer.

thanx


Example 1: explain with foo_id % 8

explain select * from foo where foo_id % 8 = 1;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Result  (cost=0.00..32.60 rows=4 width=164)
   ->  Append  (cost=0.00..32.60 rows=4 width=164)
         ->  Seq Scan on foo  (cost=0.00..16.30 rows=2 width=164)
               Filter: ((foo_id % 8) = 1)
         ->  Seq Scan on foo_1 foo  (cost=0.00..16.30 rows=2 width=164)
               Filter: ((foo_id % 8) = 1)
(6 rows)

explain select * from facts where identifier_id % 8 in ( 1, 3 );
                                 QUERY PLAN
----------------------------------------------------------------------------
 Result  (cost=0.00..48.90 rows=12 width=164)
   ->  Append  (cost=0.00..48.90 rows=12 width=164)
         ->  Seq Scan on facts  (cost=0.00..16.30 rows=4 width=164)
               Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
         ->  Seq Scan on facts_1 facts  (cost=0.00..16.30 rows=4 width=164)
               Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))
         ->  Seq Scan on facts_3 facts  (cost=0.00..16.30 rows=4 width=164)
               Filter: ((identifier_id % 8) = ANY ('{1,3}'::integer[]))



Example 2: explain with foo_id = 1

explain select * from foo where foo_id = 1;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Result  (cost=4.27..131.61 rows=18 width=164)
   ->  Append  (cost=4.27..131.61 rows=18 width=164)
         ->  Bitmap Heap Scan on foo  (cost=4.27..9.61 rows=2 width=164)
               Recheck Cond: (foo_id = 1)
               ->  Bitmap Index Scan on foo_foo_id  (cost=0.00..4.27 rows=2 width=0)
                     Index Cond: (foo_id = 1)
         ->  Seq Scan on foo_0 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_1 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_2 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_3 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_4 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_5 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_6 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
         ->  Seq Scan on foo_7 foo  (cost=0.00..15.25 rows=2 width=164)
               Filter: (foo_id = 1)
(22 rows)

--
Steven Lembark                                            85-09 90th St.
Workhorse Computing                                 Woodhaven, NY, 11421
lembark@wrkhors.com                                      +1 888 359 3508