Re: BUG #6662: Database do not push condition to subquery, test case for bug 6658

maxim(dot)boguk(at)gmail(dot)com writes:
> I managed create simple self-contained test case for 6658.

This works fine in HEAD. The reason it doesn't work fine in 9.1 (or
9.0) is that in those branches, anytextcat() and textanycat() are marked
volatile, for reasons that were good at the time but were superseded by
later policy changes. So you have a subselect containing a volatile
output expression, which prevents flattening of the subselect, so the
restriction clause doesn't get pushed down to where it could be used
with the index. For some history see
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c82d931dd180965a9a0c06acc764404f91de8170
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=aab353a60b95aadc00f81da0c6d99bde696c4b75
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3db6524fe63f0598dcb2b307bb422bc126f2b15d

If this is a big problem for you, you could safely adjust the
provolatile marking of those functions by hand in 9.1. It might be
better just to include an explicit cast to text, though, instead of
relying on the assumption that the system will let you concat an integer
directly to a text string.

regards, tom lane