2010/4/29 Teodor Sigaev <teo...@sigaev.ru>: > Hi! > > There is some strange on current CVS with correct choosing of scans.
Also true with 8.4, default configuration. > 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 > -- Cédric Villemain -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
