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

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

From: "Michael Artz" <mlartz(at)gmail(dot)com>
To: "me(at)alternize(dot)com" <me(at)alternize(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: 8.1 (win32): partial index not used?
Date: 2006-04-28 01:39:27
Message-ID: e9c163070604271839j6f65d4f7sb2448f4221e9147f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I'm still new at this as well, but I'm guessing that the query planner can't
extrapolate that (lower(mov_folder) = 'harrypotter5') also implies
((mov_folder)::text <> ''::text).  Try the query:

SELECT * FROM oldtables.movies WHERE mov_year = 2007 AND
lower(mov_folder) = 'harrypotter5' AND (mov_folder)::text <> ''::text

I'm thinking that that will allow the planner to match the WHERE and use the
index.

-Mike

On 4/27/06, me(at)alternize(dot)com <me(at)alternize(dot)com> wrote:
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

In response to

pgsql-novice by date

Next:From: meDate: 2006-04-28 01:42:30
Subject: Re: 8.1 (win32): partial index not used?
Previous:From: meDate: 2006-04-28 01:29:58
Subject: 8.1 (win32): partial index not used?

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