Hi!
There is some strange on current CVS with correct choosing of scans. Although
bitmap scan is cheaper but postgresql chooses seqscan. Test suite:
CREATE OR REPLACE FUNCTION genvect()
RETURNS tsvector AS
$$
SELECT
array_to_string(
ARRAY(
SELECT
(random()*random()*random()*1000.0)::int::text
FROM
generate_series(1, 10 + (100.0*random())::bigint)
),
' '
)::tsvector;
$$
LANGUAGE SQL VOLATILE;
SELECT
t::int4 AS id, genvect() AS ts INTO foo
FROM
generate_series(1, 100000) AS t;
CREATE INDEX foo_idx ON foo USING gin (ts);
VACCUM ANALYZE foo;
postgres=# explain select count(*) from foo where ts @@ '259';
QUERY PLAN
---------------------------------------------------------------
Aggregate (cost=5817.27..5817.28 rows=1 width=0)
-> Seq Scan on foo (cost=0.00..5805.00 rows=4907 width=0)
Filter: (ts @@ '''259'''::tsquery)
(3 rows)
Time: 6,370 ms
postgres=# set enable_seqscan = off;
SET
Time: 2,014 ms
postgres=# explain select count(*) from foo where ts @@ '259';
QUERY PLAN
---------------------------------------------------------------------------------
Aggregate (cost=5767.35..5767.36 rows=1 width=0)
-> Bitmap Heap Scan on foo (cost=942.46..5755.08 rows=4907 width=0)
Recheck Cond: (ts @@ '''259'''::tsquery)
-> Bitmap Index Scan on foo_idx (cost=0.00..941.24 rows=4907 width=0)
Index Cond: (ts @@ '''259'''::tsquery)
(5 rows)
Why does pgsql choose seqscan (5817.28) instead of bitmap one (5767.36)?
Changed options in postgresql.conf:
shared_buffers=128MB
temp_buffers=16MB
work_mem=16MB
maintenance_work_mem=256MB
effective_cache_size=1024MB
--
Teodor Sigaev E-mail: teo...@sigaev.ru
WWW: http://www.sigaev.ru/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
