From: | "Alex" <alex(at)xdcom(dot)org> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4799: BitMapAnd never works with gin |
Date: | 2009-05-08 09:31:28 |
Message-ID: | 200905080931.n489VSDi025406@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4799
Logged by: Alex
Email address: alex(at)xdcom(dot)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=1;"
-----------------------------------------
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alex | 2009-05-08 10:36:18 | BUG #4800: constraint_exclusion could be smarter with bool conversion |
Previous Message | Michal Szymanski | 2009-05-08 09:23:37 | Re: 42804: structure of query does not match error where using RETURN QUERY |