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

8.1 (win32): partial index not used?

From: <me(at)alternize(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: 8.1 (win32): partial index not used?
Date: 2006-04-28 01:29:58
Message-ID: 12bf01c66a63$43c15c90$6402a8c0@iwing (view raw or flat)
Thread:
Lists: 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 



Responses

pgsql-novice by date

Next:From: Michael ArtzDate: 2006-04-28 01:39:27
Subject: Re: 8.1 (win32): partial index not used?
Previous:From: Bruno Wolff IIIDate: 2006-04-27 19:48:03
Subject: Re: error handling

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