Re: WHERE with ORDER not using the best index

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: WHERE with ORDER not using the best index
Дата
Msg-id 9852.1391037355@sss.pgh.pa.us
обсуждение исходный текст
Ответ на WHERE with ORDER not using the best index  (jugnooken <ken@jugnoo.com>)
Ответы Re: WHERE with ORDER not using the best index  (jugnooken <ken@jugnoo.com>)
Список pgsql-performance
jugnooken <ken@jugnoo.com> writes:
> Here's the query:

> db=> EXPLAIN ANALYSE SELECT social_feed_feed_items.social_message_id FROM
> social_feed_feed_items WHERE social_feed_feed_items.social_feed_id = 480
> ORDER BY posted_at DESC NULLS LAST LIMIT 1200;

> QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=126.83..127.43 rows=1200 width=12) (actual time=10.321..13.694
> rows=1200 loops=1)
>    ->  Sort  (cost=126.83..129.08 rows=4498 width=12) (actual
> time=10.318..11.485 rows=1200 loops=1)
>          Sort Key: posted_at
>          Sort Method: top-N heapsort  Memory: 153kB
>          ->  Index Scan using index_social_feed_feed_items_on_social_feed_id
> on social_feed_feed_items  (cost=0.09..76.33 rows=4498 width=12) (actual
> time=0.037..5.317 rows=4249 loops=1)
>                Index Cond: (social_feed_id = 480)
>  Total runtime: 14.913 ms
> (7 rows)

> I was hoping that they planner would use
> index_social_feed_feed_items_on_social_feed_id_and_posted_at, but it never
> does. If I manually remove the index that it currently uses then magic
> happens:

> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.09..998.63 rows=1200 width=12) (actual time=0.027..3.792
> rows=1200 loops=1)
>    ->  Index Scan using
> index_social_feed_feed_items_on_social_feed_id_and_posted_at on
> social_feed_feed_items  (cost=0.09..3742.95 rows=4498 width=12) (actual
> time=0.023..1.536 rows=1200 loops=1)
>          Index Cond: (social_feed_id = 480)
>  Total runtime: 4.966 ms
> (4 rows)

Well, it likes the first plan because it's estimating that one as cheaper
;-).  The question is why the indexscan cost is estimated so remarkably
high for the second index --- nearly two orders of magnitude more to
retrieve the same number of index entries.  The most obvious explanation
is that that index is horribly bloated for some reason.  Have you checked
the physical index sizes?  If the second index is many times bigger,
REINDEX ought to help, though it's unclear whether the bloat will recur.

            regards, tom lane


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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Select hangs and there are lots of files in table and index directories.
Следующее
От: Дмитрий Шалашов
Дата:
Сообщение: trick the query optimiser to skip some optimisations