Re: 8.1 (win32): partial index not used? - Mailing list pgsql-novice

From
Subject Re: 8.1 (win32): partial index not used?
Date
Msg-id 12c401c66a65$03a194c0$6402a8c0@iwing
Whole thread Raw
In response to 8.1 (win32): partial index not used?  (<me@alternize.com>)
List 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



pgsql-novice by date:

Previous
From: "Michael Artz"
Date:
Subject: Re: 8.1 (win32): partial index not used?
Next
From:
Date:
Subject: Re: 8.1 (win32): partial index not used?