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

<me(at)alternize(dot)com> writes:
> 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

> why isn't the index in queryplan2 used anymore?

Because the planner can't prove that the query only needs to access rows
satisfying the index's condition. If you wrote

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

then it'd work.

If you're expecting the planner to deduce mov_folder <> '' from
lower(mov_folder) = 'harrypotter5', sorry, that ain't happening.
That requires at least one piece of knowledge the planner does
not possess (that lower('') can't yield 'harrypotter5'), and even
with that knowledge available there'd be way more searching to make
this proof than we can afford to indulge in during the planning process.

I think it would work with that query if you'd made the partial index
predicate be WHERE lower(mov_folder) <> ''. That matches the query
closely enough that the planner will figure out that what it needs to
check is whether 'harrypotter5' <> ''.

regards, tom lane