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