Re: trick the query optimiser to skip some optimisations

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