From: | Hannu Valtonen <Hannu(dot)Valtonen(at)hut(dot)fi> |
---|---|
To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3639: queryplanner degrades to a sequential scan even if there's an index |
Date: | 2007-09-27 10:41:40 |
Message-ID: | 46FB88E4.5060906@hut.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Heikki Linnakangas kirjoitti:
> 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.
>
Ah, thank you very much. I was specifically interested in the != 1
chunk of table1. And that now works with the
AND NOT (table1.table2_id = 1)
- Hannu
From | Date | Subject | |
---|---|---|---|
Next Message | Stéphane Schildknecht | 2007-09-27 12:24:24 | Re: CREATE USER and createuser not working the same |
Previous Message | Heikki Linnakangas | 2007-09-27 10:17:10 | Re: BUG #3639: queryplanner degrades to a sequential scan even if there's an index |