Skip site navigation (1) Skip section navigation (2)

Re: BUG #4798: BitMapAnd never works with gin

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Alex" <alex(at)xdcom(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4798: BitMapAnd never works with gin
Date: 2009-05-08 17:08:33
Message-ID: 9942.1241802513@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Alex" <alex(at)xdcom(dot)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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-05-08 17:26:47
Subject: Re: Re: 42804: structure of query does not match error where using RETURN QUERY
Previous:From: Michal SzymanskiDate: 2009-05-08 16:19:47
Subject: Re: 42804: structure of query does not match error where using RETURN QUERY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group