Bad planner decision - bitmap scan instead of index - Mailing list pgsql-performance

Hello everyone,

This being my first e-mail to the mailing list, I hope my question is
relevant and on-topic. I'm seeing poor performance on a few queries
where the planner decides to use a bitmap scan instead of using indices.

I'm using a stock PostgreSQL 8.1.9 on Debian 4.0r0 (x86). The
database is vacuumed and analyzed daily ("full") and testing on a
different machine (stock 8.1.9 on Ubuntu 6.06 LTS (x86)) gave the
same results. I've made sure the data for each query was cached.

The (example) query:
SELECT * FROM movies WHERE letter = 'T' ORDER BY name ASC LIMIT 100
OFFSET 1900;
is run on a single table "movies" (~ 250.000 rows) with the following
structure and indices:

                                        Table "public.movies"
   Column  |            Type             |
Modifiers
----------+-----------------------------
+-----------------------------------------------------------
movie_id | integer                     | not null default nextval
('movies_movie_id_seq'::regclass)
name     | character varying(255)      | not null
year     | integer                     | not null
views    | integer                     | not null default 0
lastview | timestamp without time zone |
letter   | character(1)                | not null default 'A'::bpchar
Indexes:
     "movies_pkey" PRIMARY KEY, btree (movie_id)
     "movies_lastview" btree (lastview)
     "movies_letter" btree (letter)
     "movies_letter_name" btree (letter, name)
     "movies_name" btree (name)
     "movies_year" btree ("year")

Running the query using EXPLAIN ANALYZE results in the following
query plan and execution time:

Limit  (cost=4002.04..4002.29 rows=100 width=48) (actual
time=1469.565..1470.097 rows=100 loops=1)
    ->  Sort  (cost=3997.29..4031.18 rows=13556 width=48) (actual
time=1460.958..1467.993 rows=2000 loops=1)
          Sort Key: name
          ->  Bitmap Heap Scan on movies  (cost=86.45..3066.90
rows=13556 width=48) (actual time=20.522..77.889 rows=13640 loops=1)
                Recheck Cond: (letter = 'T'::bpchar)
                ->  Bitmap Index Scan on movies_letter
(cost=0.00..86.45 rows=13556 width=0) (actual time=18.452..18.452
rows=13658 loops=1)
                      Index Cond: (letter = 'T'::bpchar)
Total runtime: 1474.821 ms

Setting enable_bitmapscan to 0 results in the following plan and
execution time:

Limit  (cost=5041.06..5306.38 rows=100 width=48) (actual
time=15.385..16.305 rows=100 loops=1)
    ->  Index Scan using movies_letter_name on movies
(cost=0.00..35966.65 rows=13556 width=48) (actual time=0.121..14.067
rows=2000 loops=1)
          Index Cond: (letter = 'T'::bpchar)
Total runtime: 16.604 ms

Seeing that disabling the bitmap scan speeds up the query about fifty
times, it would be interesting to know what is causing the planner to
decide to not use the appropriate index.

If anyone could comment a bit on my example, that would be great.
There's a few things I'm considering regarding this:
- I could disable bitmap scan altogether, per application or query,
but that does not seem elegant, I'd rather have the query planner
make better decisions
- I could try and test what 8.2 does if someone expects the results
to be different, but I can't yet upgrade my production servers to 8.2
- am I just running into a corner case which falls outside of the
planner's logic?

Thanks in advance for your efforts and replies.

With kind regards,

Frank Schoep