BUG #4798: BitMapAnd never works with gin

Поиск
Список
Период
Сортировка
От Alex
Тема BUG #4798: BitMapAnd never works with gin
Дата
Msg-id 200905080912.n489CCNn016745@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #4798: BitMapAnd never works with gin
Список pgsql-bugs
The following bug has been logged online:

Bug reference:      4798
Logged by:          Alex
Email address:      alex@xdcom.org
PostgreSQL version: 8.3.6
Operating system:   rhel5
Description:        BitMapAnd never works with gin
Details:

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;
-----------------------------------------

"select count(0) from foo where gender"
-----------------------------------------
100,000.

"select relname,relpages,relkind,reltuples from pg_class where relname ~
'name_idx';"
-----------------------------------------
 name_idx |      280 | i       |    100000

"SELECT count(id) FROM foo where gender = 1  and
"to_tsvector('english',name) @@ 'alex'"
-----------------------------------------
4000
"SELECT count(id) FROM foo where gender = 1  and
"to_tsvector('english',nick) @@ 'ali'"
-----------------------------------------
3000

"EXPLAIN SELECT id FROM foo where gender = 1  and
to_tsvector('english',name) @@ 'alex' and to_tsvector('english',nick) @@
'ali';"
-----------------------------------------
 Bitmap Heap Scan on foo  (cost=4.37..63.85 rows=1 width=4)
   Recheck Cond: ((to_tsvector('english'::regconfig, (name)::text) @@
'''alex'''::tsquery) AND (gender = 1))
   Filter: (to_tsvector('english'::regconfig, (nick)::text) @@
'''ali'''::tsquery)
   ->  Bitmap Index Scan on name_idx  (cost=0.00..4.37 rows=15 width=0)
         Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@
'''alex'''::tsquery)

The actual cost is exhausting.
I think the gepo should give BitMapAnd better.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Dave Page
Дата:
Сообщение: Re: BUG #4797: Uninstall : impossible to delete some files (base...)
Следующее
От: Michal Szymanski
Дата:
Сообщение: 42804: structure of query does not match error where using RETURN QUERY