Alexander Kukushkin <cyberdemn@gmail.com> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general