"Alex" <alex@xdcom.org> writes:
> CREATE TABLE foo
> (
> id serial NOT NULL,
> name character varying(32),
> nick character varying(32),
> gender integer
> )WITH (OIDS=FALSE);
> CREATE INDEX name_idx
> ON foo
> USING gin(to_tsvector('english'::regconfig, name))
> WHERE gender = 1;
> CREATE INDEX nick_idx
> ON foo
> USING gin(to_tsvector('english'::regconfig, nick))
> WHERE gender = 1;
GIN is not relevant --- the problem is the WHERE clauses. The planner
won't use these two indexes together in a BitmapAnd because they have
identical predicates. Per comments in choose_bitmap_and:
* We will only consider AND combinations in which no two indexes use the
* same WHERE clause. This is a bit of a kluge: it's needed because
* costsize.c and clausesel.c aren't very smart about redundant clauses.
* They will usually double-count the redundant clauses, producing a
* too-small selectivity that makes a redundant AND step look like it
* reduces the total cost. Perhaps someday that code will be smarter and
* we can remove this limitation. (But note that this also defends
* against flat-out duplicate input paths, which can happen because
* best_inner_indexscan will find the same OR join clauses that
* create_or_index_quals has pulled OR restriction clauses out of.)
*
* For the same reason, we reject AND combinations in which an index
* predicate clause duplicates another clause. Here we find it necessary
* to be even stricter: we'll reject a partial index if any of its
* predicate clauses are implied by the set of WHERE clauses and predicate
* clauses used so far. This covers cases such as a condition "x = 42"
* used with a plain index, followed by a clauseless scan of a partial
* index "WHERE x >= 40 AND x < 50". The partial index has been accepted
* only because "x = 42" was present, and so allowing it would partially
* double-count selectivity. (We could use predicate_implied_by on
* regular qual clauses too, to have a more intelligent, but much more
* expensive, check for redundancy --- but in most cases simple equality
* seems to suffice.)
My advice is to drop one or both of the index WHERE clauses --- it's not
apparent that they're really good for much in an example like this.
regards, tom lane