Alexander Kukushkin <cyberdemn@xxxxxxxxx> writes: > Original problem has been found on 9.4.9, but I was able to reproduce it on > 10rc1. The problem here is that the generic estimate of the selectivity of "where value = $1" is 1.0, because var_eq_non_const makes the not unreasonable assumption that the unknown value being searched for is one of the values appearing in the table, and there is only one. This makes the estimated cost of the seqscan+limit plan nearly nil, since it's expected to return the first row it comes to. So that plan gets chosen if we're considering a generic plan that doesn't know the specific value of $1. And at that point the plancache says to itself "I'm not getting any benefit in estimated cost for the custom plans I've been making, so I'll stop doing that". This is not an easy thing to improve without making other cases worse :-(. One heuristic that I've been wondering about is whether we could say "if the generic plan appears cheaper than any custom plan we've made so far, disbelieve it, because probably it's based on wrong estimates". But I'm not sure if that would have its own failure modes. Anyway nobody's tried it yet. You can find more discussion of this problem in the -hackers archives. As for workarounds, the only short-term fix I can suggest is to use EXECUTE for this query in your function, thus preventing caching of a plan for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
