Re: Bad planner decision - bitmap scan instead of index

Поиск
Список
Период
Сортировка
От Frank Schoep
Тема Re: Bad planner decision - bitmap scan instead of index
Дата
Msg-id 8797DC0C-8D0D-4F2D-A81B-4B65F8FC7382@ffnn.nl
обсуждение исходный текст
Ответ на Re: Bad planner decision - bitmap scan instead of index  (hubert depesz lubaczewski <depesz@depesz.com>)
Ответы Re: Bad planner decision - bitmap scan instead of index  (Michael Stone <mstone+postgres@mathom.us>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Bad planner decision - bitmap scan instead of index
Следующее
От: Michael Stone
Дата:
Сообщение: Re: Bad planner decision - bitmap scan instead of index