Slow query question

Поиск
Список
Период
Сортировка
От Andrey Povazhnyi
Тема Slow query question
Дата
Msg-id 48E80576-4047-436C-97DD-59EA7BE4DE34@gmail.com
обсуждение исходный текст
Ответы Re: Slow query question
Список pgsql-performance
Hello, List.

We’ve got a strange planner behavior on a query to one of our bigger tables after we upgraded to postgres 9.6.1 recently.
The table (schema here http://pastebin.com/nRAny4bw) has 28m+ rows and is used to store chat messages for different chat rooms (symbol is the room id).
The query is as follows:
SELECT "tv_site_chathistory"."source" FROM "tv_site_chathistory" WHERE "tv_site_chathistory"."symbol" = ’pm_OmoGVzBdyPnpYkXD' ORDER BY "tv_site_chathistory"."id" DESC LIMIT 30;
(explain analyze is here https://explain.depesz.com/s/iyT)

For some reason planner chooses to scan using pkey index instead of index on symbol column. Most times it uses the right index, but for this particular ‘symbol’ value is resorts to pkey scan. One possible clue could be that last 30 rows with this particular symbol are spanning some relatively large time of creation.

Any advice would be greatly appreciated!

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

Предыдущее
От: Marc-Olaf Jaschke
Дата:
Сообщение: Re: performance issue with bitmap index scans on huge amounts of big jsonb documents
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Slow query question