From: | <me(at)alternize(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: 8.1 (win32): partial index not used? |
Date: | 2006-04-28 01:42:30 |
Message-ID: | 12c401c66a65$03a194c0$6402a8c0@iwing |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | me | 2006-04-28 01:45:32 | Re: 8.1 (win32): partial index not used? |
Previous Message | Michael Artz | 2006-04-28 01:39:27 | Re: 8.1 (win32): partial index not used? |