Tom, Thank you for a thorough answer. We’ll try the 2-column index. Regards, Andrey Povazhnyi > On Dec 6, 2016, at 6:33 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > Andrey Povazhnyi <w0rse.t@xxxxxxxxx> writes: >> 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 basic problem with this query is that there are no good alternatives. > The planner believes there are about 53K rows matching the WHERE > condition. (I assume this estimate is roughly in line with reality, > else we have different problems to talk about.) It can either scan down > the "id" index and stop when it finds the 30th row matching WHERE, or > it can use the "symbol" index to read all 53K rows matching WHERE and > then sort them by "id". Neither one of those is going to be speedy; > but the more rows there are matching WHERE, the better the first way > is going to look. > > If you're worried about doing this a lot, it might be worth your while > to provide a 2-column index on (source, id) --- in that order --- which > would allow a query plan that directly finds the required 30 rows as > consecutive index entries. Possibly this could replace your index on > "source" alone, depending on how much bigger the 2-col index is and > how many queries have no use for the second column. See > https://www.postgresql.org/docs/current/static/indexes.html > particularly 11.3 - 11.5. > > regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
