Re: trick the query optimiser to skip some optimisations

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: trick the query optimiser to skip some optimisations
Дата
Msg-id CAMkU=1yvcj2cWUTG_Y4aOZXZkMiqBmCbg_cCQP5EtJRqUXABeg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: trick the query optimiser to skip some optimisations  (Дмитрий Шалашов <skaurus@gmail.com>)
Список pgsql-performance
Дерево обсуждения
On Wed, Jan 29, 2014 at 4:17 PM, Дмитрий Шалашов <skaurus@gmail.com> wrote:
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)

Ah, of course.  It prevents the optimization you want, as well as the one you don't want.

This is getting very ugly, but maybe change the index to match the degenerate query:

"feed_user_id_added_idx3" btree ((user_id + 0), added DESC) WHERE active_id = user_id AND type = 1

Long term I would probably look into refactoring the table so that "active_id = user_id" is not a magical condition, like it seems to be for you currently.  Maybe introduce a boolean column.

Cheers,

Jeff

В списке pgsql-performance по дате отправления:

Предыдущее
От: Sergey Konoplev
Дата:
Сообщение: Re: trick the query optimiser to skip some optimisations
Следующее
От: Julien Cigar
Дата:
Сообщение: Re: PostgreSQL 9.3.2 Performance tuning for 32 GB server