8.1 (win32): partial index not used?
От | |
---|---|
Тема | 8.1 (win32): partial index not used? |
Дата | |
Msg-id | 12bf01c66a63$43c15c90$6402a8c0@iwing обсуждение исходный текст |
Ответы |
Re: 8.1 (win32): partial index not used?
Re: 8.1 (win32): partial index not used? |
Список | pgsql-novice |
hi list i've noticed a problem with an index, where the index wouldn't be used anymore as soon as a partial condition is added. here's the setup: table: "oldtables.movies" containing around 50'000 entries. a small percentage (roughly 4000) have a value for the columns "mov_year" (int, default: 0) and "mov_folder" (varchar, default: '') indices: movies_mov_year_idx: USING btree ("mov_year"); movies_folder_idx: USING btree (lower((mov_folder)::text)); query: SELECT * FROM oldtables.movies WHERE mov_year = 2007 AND lower(mov_folder) = 'harrypotter5' LIMIT 1 queryplan1: Bitmap Heap Scan on movies (cost=5.67..9.68 rows=1 width=232) (actual time=0.197..0.198 rows=1 loops=1) Recheck Cond: ((lower((mov_folder)::text) = 'harrypotter5'::text) AND (mov_year = 2007)) -> BitmapAnd (cost=5.67..5.67 rows=1 width=0) (actual time=0.191..0.191 rows=0 loops=1) -> Bitmap Index Scan on movies_folder_idx (cost=0.00..2.64 rows=183 width=0) (actual time=0.089..0.089 rows=1 loops=1) Index Cond: (lower((mov_folder)::text) = 'harrypotter5'::text) -> Bitmap Index Scan on movies_mov_year_idx (cost=0.00..2.78 rows=222 width=0) (actual time=0.097..0.097 rows=62 loops=1) Index Cond: (mov_year = 2007) Total runtime: 0.274 ms i thought of changing the movies_folder_idx to a partial index for speeding up the query: movies_folder_idx: USING btree ((lower((mov_folder)::text))) WHERE ((mov_folder)::text <> ''::text); after adding the index, i've issued a reindex and ran the query again: query: SELECT * FROM oldtables.movies WHERE mov_year = 2007 AND lower(mov_folder) = 'harrypotter5' LIMIT 1 queryplan2: Bitmap Heap Scan on movies (cost=2.78..581.78 rows=1 width=232) (actual time=0.440..0.630 rows=1 loops=1) Recheck Cond: (mov_year = 2007) Filter: (lower((mov_folder)::text) = 'harrypotter5'::text) -> Bitmap Index Scan on movies_mov_year_idx (cost=0.00..2.78 rows=222 width=0) (actual time=0.182..0.182 rows=62 loops=1) Index Cond: (mov_year = 2007) Total runtime: 0.768 ms why isn't the index in queryplan2 used anymore? on a sidenote, the query runtimes seem in both plans way to high for a relatively small table. what settings would i have to tweak to get better results? thanks, thomas
В списке pgsql-novice по дате отправления: