Re: Choosing between seqscan and bitmap scan

2010/4/29 Teodor Sigaev <teodor(at)sigaev(dot)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: teodor(at)sigaev(dot)ru
>                                                   WWW: http://www.sigaev.ru/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cédric Villemain