> i've noticed a problem with an index, where the index wouldn't be used
> anymore as soon as a partial condition is added.
please ignore my partial index problem. of course i would have to define the
partial index part correctly:
it works just fine this way:
movies_folder_idx: USING btree ((lower((mov_folder)::text))) WHERE
(lower((mov_folder)::text) <> ''::text);
still, 0.2ms for querying an index containing ~3000 entries seems quite
long:
SELECT * FROM oldtables.movies WHERE lower(mov_folder) = 'harrypotter5' AND
mov_year = 2007 LIMIT 1
Limit (cost=5.67..9.68 rows=1 width=232) (actual time=0.084..0.084 rows=1
loops=1)
-> Bitmap Heap Scan on movies (cost=5.67..9.68 rows=1 width=232) (actual
time=0.081..0.081 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.075..0.075 rows=0 loops=1)
-> Bitmap Index Scan on movies_folder_idx (cost=0.00..2.64
rows=182 width=0) (actual time=0.034..0.034 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.036..0.036 rows=62 loops=1)
Index Cond: (mov_year = 2007)
Total runtime: 0.217 ms
any tipps?
- thomas