Re: Bad planner decision - bitmap scan instead of index

[Frank Schoep <frank(at)ffnn(dot)nl>]

On Aug 17, 2007, at 9:28 AM, hubert depesz lubaczewski wrote:
> …
> try to change the query to:
> SELECT * FROM movies WHERE letter = 'T' ORDER BY letter ASC, name  
> ASC LIMIT 100
> OFFSET 1900;

Thanks for the suggestion, however executing this query takes even  
longer regardless of work_mem. The query leads to this plan:

Limit  (cost=4320.68..4320.93 rows=100 width=48) (actual  
time=2137.764..2138.294 rows=100 loops=1)
   ->  Sort  (cost=4315.93..4351.49 rows=14221 width=48) (actual  
time=2129.755..2136.184 rows=2000 loops=1)
         Sort Key: letter, name
         ->  Bitmap Heap Scan on movies  (cost=90.77..3067.54  
rows=14221 width=48) (actual time=20.277..89.913 rows=13640 loops=1)
               Recheck Cond: (letter = 'T'::bpchar)
               ->  Bitmap Index Scan on movies_letter   
(cost=0.00..90.77 rows=14221 width=0) (actual time=18.139..18.139  
rows=13644 loops=1)
                     Index Cond: (letter = 'T'::bpchar)
Total runtime: 2143.111 ms

To compare, that same query (sorting by two columns) without bitmap  
scan runs like this:

Limit  (cost=5025.26..5289.75 rows=100 width=48) (actual  
time=14.986..15.911 rows=100 loops=1)
   ->  Index Scan using movies_letter_name on movies   
(cost=0.00..37612.76 rows=14221 width=48) (actual time=0.125..13.686  
rows=2000 loops=1)
         Index Cond: (letter = 'T'::bpchar)
Total runtime: 16.214 ms

I'm not an expert at how the planner decides which query plan to use,  
but it seems that in my (corner?) case bitmap scan shouldn't be  
preferred over the index scan, as the index is pre-sorted and spans  
all columns involved in the 'WHERE' and 'ORDER BY' clauses.

Regarding the sort performance and work_mem size I have tested the  
same scenario on a second machine (dual P3-1.13 GHz) with work_mem  
set to 8192, using the same PostgreSQL version. The query plans are  
identical and running times are ~300ms for the bitmap scan and ~5ms  
for index scan.

Sincerely,

Frank