> For "inserts" I do not see the reason
> why
> it would be better to use index partitioning because AFAIK
> b-tree
> would behave exactly the same in both cases.
no, when the index gets very big inserting random values gets
very slow.
But still, my approach doesn't work because I thought Postgresql
was able to "merge" different partial indexes (using
BitmapOr/BitmapAnd) when the WHERE condition matches multiple
partial indexes... but that's (I guess) not that easy to do
(basically partial indexes condition should be checked like the
constraint of the inheritance mechanism, and Or/And Bitmapped
as happens in the "regular" partitioning method).
that is, having a table with:
CREATE TABLE test
(
a timestamp without time zone,
b integer
)
CREATE INDEX test1idx
ON test
(b)
WHERE a >= '2008-03-10 14:00:00' AND a < '2008-03-10 16:00:00';
CREATE INDEX test2idx
ON test
(b)
WHERE a >= '2008-03-10 16:00:00' AND a < '2008-03-10 18:00:00';
the select:
select * from test where a > '2008-03-10 15:00:00'
and a < '2008-03-10 17:00:00' and b = 100
should use a BitmapOr between an index scan on test1idx and test2idx...
But I think that's a complicated thing for the planner... even though
it doesn't sound that different from what the planner does for partition
pruning...
