"Zeugswetter Andreas DAZ SD" <ZeugswetterA@spardat.at> writes:
> Workable examples for useful partitioned indexes, that help here are:
> create index people_male_ix on people (city) where gender = 'male';
> create index people_gay_ix on people (city) where orientation = 'gay';
> create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay';
> Note, that the indexed column differs from the partitioning clause.
> Note also, that the last index will perform way better than a combo of bitmap indexes.
This is definitely a useful technique in some cases, but it's got its
limits. You have to have only a fairly small number of interesting
conditions (else the number of indexes gets out of hand) and those
conditions have to be spelled out explicitly in the query. That is,
the last index will indeed work forSELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city =
'SanFrancisco';
but it will not work forSELECT * FROM people WHERE orientation = $1 AND gender = $2 AND city = $3;
which is the sort of thing that the planner is increasingly going to
have to deal with.
Combining bitmaps at runtime is certainly somewhat more expensive to
execute, but it can deal with cases where the specific values being
searched for are not known until runtime.
regards, tom lane
