From: | Дмитрий Шалашов <skaurus(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | trick the query optimiser to skip some optimisations |
Date: | 2014-01-29 23:38:01 |
Message-ID: | CAKPeCUEDnLhzW8TFWBjUgW5HPY_pFQ+1oCS-F1fxn9mFwk4KFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi!
I have a table called 'feed'. It's a big table accessed by many types of
queries, so I have quite a lot of indices on it.
Those that are relevant looks like this:
"feed_user_id_active_id_added_idx" btree (user_id, active_id, added)
"feed_user_id_added_idx" btree (user_id, added DESC)
"feed_user_id_added_idx2" btree (user_id, added DESC) WHERE active_id =
user_id AND type = 1
last one is very small and tailored for the specific query.
"added" field is timestamp, everything else is integers.
That specific query looks like this:
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.
I have PostgreSQL 9.2.6 on Debian.
Best regards,
Dmitriy Shalashov
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-01-29 23:50:00 | Re: trick the query optimiser to skip some optimisations |
Previous Message | Tom Lane | 2014-01-29 23:15:55 | Re: WHERE with ORDER not using the best index |