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@xxxxxxxxxxx +1 888 359 3508
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
