From: | Дмитрий Шалашов <skaurus(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: trick the query optimiser to skip some optimisations |
Date: | 2014-01-30 00:17:23 |
Message-ID: | CAKPeCUFxPfAtYKJk1sVh_Upe3YYH-kyd4f_fpZ_PnNyvXa0+NA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Thanks for the tip!
Well, index is now used but...
Limit (cost=264291.67..264291.75 rows=31 width=50)
-> Sort (cost=264291.67..264292.80 rows=453 width=50)
Sort Key: added
-> Bitmap Heap Scan on feed (cost=1850.99..264278.18 rows=453
width=50)
Recheck Cond: ((active_id = user_id) AND (type = 1))
Filter: ((user_id + 0) = 7)
-> Bitmap Index Scan on feed_user_id_added_idx2
(cost=0.00..1850.88 rows=90631 width=0)
Best regards,
Dmitriy Shalashov
2014-01-30 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
> On Wed, Jan 29, 2014 at 3:38 PM, Дмитрий Шалашов <skaurus(at)gmail(dot)com>wrote:
>
>
>> "feed_user_id_added_idx2" btree (user_id, added DESC) WHERE active_id =
>> user_id AND type = 1
>>
>
> ...
>
>
>> SELECT * FROM feed WHERE user_id = ? AND type = 1 AND active_id = user_id
>> ORDER BY added DESC LIMIT 31;
>>
>> But it doesn't use the last index. EXPLAIN shows this:
>>
>> Limit (cost=0.00..463.18 rows=31 width=50)
>> -> Index Scan Backward using feed_user_id_active_id_added_idx on
>> user_feed (cost=0.00..851.66 rows=57 width=50)
>> Index Cond: ((user_id = 7) AND (active_id = 7))
>> Filter: (type = 1)
>>
>> So as we can see optimiser changes "active_id = user_id" to "active_id =
>> <whatever value user_id takes>". And it brokes my nice fast partial index :(
>> Can I do something here so optimiser would use the
>> feed_user_id_added_idx2 index? It's around ten times smaller than the
>> 'generic' feed_user_id_active_id_added_idx index.
>>
>
> How about "where user_id+0=?"
>
> Cheers,
>
> Jeff
>
From | Date | Subject | |
---|---|---|---|
Next Message | jugnooken | 2014-01-30 01:47:19 | Re: WHERE with ORDER not using the best index |
Previous Message | Jeff Janes | 2014-01-29 23:50:00 | Re: trick the query optimiser to skip some optimisations |