Slow query question

From: Andrey Povazhnyi <w0rse(dot)t(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow query question
Date: 2016-12-06 14:35:23
Message-ID: 48E80576-4047-436C-97DD-59EA7BE4DE34@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 <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 <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!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2016-12-06 15:33:55 Re: Slow query question
Previous Message Marc-Olaf Jaschke 2016-12-05 14:51:19 Re: performance issue with bitmap index scans on huge amounts of big jsonb documents