Re: BUG #3639: queryplanner degrades to a sequential scan even if there's an index

Hannu Valtonen wrote:
> explain analyze select * from table1, table2 where table1.table2_id =
> table2.id and table2.id = 2841962;

Here the planner is using the partial index table2_id_index on table1,
knowing that table1.table2_id equals 2841962, satisfying the "NOT
(table2_id = 1)" condition that index has.

> explain analyze select * from table1, table2 where table1.table2_id =
> table2.id and lower(table2.name) = lower('nicestring');

But here, table1.table2_id can have any value, including 1, so it can't
use that index.

You can rewrite the query like this:

select * from table1, table2 where table1.table2_id = table2.id and
lower(table2.name) = lower('nicestring') AND NOT (table1.table2_id = 1)
UNION ALL
select * from table1, table2 where table1.table2_id = table2.id and
lower(table2.name) = lower('nicestring') AND (table1.table2_id = 1)

In which case the planner can use the index for the first part, though
not for the second part which might still be slow. I don't know the
schema, but perhaps you're not really interested in rows with table2_id
= 1, so you could just leave out the second part of the union.

Or you can make the index a normal, non-partial index.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com