Skip site navigation (1) Skip section navigation (2)

Re: 8.1 (win32): partial index not used?

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 (view raw or flat)
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



In response to

Responses

pgsql-novice by date

Next:From: meDate: 2006-04-28 01:45:32
Subject: Re: 8.1 (win32): partial index not used?
Previous:From: Michael ArtzDate: 2006-04-28 01:39:27
Subject: Re: 8.1 (win32): partial index not used?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group