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!
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 |