Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do?

Поиск
Список
Период
Сортировка
От Pat Maddox
Тема Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do?
Дата
Msg-id 336A009B-2A78-47CC-8D23-5630146547CC@adorable.io
обсуждение исходный текст
Ответ на Re: [PERFORM] Please help with a slow query: there are millions ofrecords, what can we do?  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-performance
> On Mar 8, 2017, at 11:00 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> You could try a partial index on:
>
> (account_id, completed_at desc, taskable_name, position, assigned_to_user_id) where "tasks"."archived" != 't' AND
"tasks"."complete"= 't' 
>
> Also, the poor estimate of the number of rows on your scan of
index_permissions_on_user_id_and_object_id_and_object_typesuggests that you are not analyzing (and so probably also not
vacuuming)often enough. 

Thanks for this. So here’s a quick update…

I removed all the indexes that are there and added one on:

(account_id, taskable_type, taskable_id, assigned_to_user_id, archived, complete, completed_at, due_on)

We search for tasks that are complete or incomplete, so we wouldn’t want a partial index there… but I _think_ changing
theindex to be partial where archived != ’t’ would be beneficial; I’ll have to look. As of today, only about 10% of the
tasksare archived=’t’ – though that’s still ~1 million rows at this point. 

That helped the query plans big time, and adding more RAM so the indexes fit in memory instead of swapping led to major
improvements.

So thank you for the suggestions :)

I’ve manually vacuumed and analyzed a few times, and the estimates are always pretty far off. How do you suggest
increasingthe stats for the table? Just increase it, vacuum, and see if the stats look better? 

Thanks,
Pat

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

Предыдущее
От: Claudio Freire
Дата:
Сообщение: Re: [PERFORM] Optimizing around retained tuples
Следующее
От: Pietro Pugni
Дата:
Сообщение: [PERFORM] Postgres not using all RAM (Huge Page activated on a 96GB RAM system)