Querying pg_operator shows that the relevant operator implementation function is anytextcat.
regress=> select l.typname, r.typname, o.oprcode
from pg_operator o
inner join pg_type l on (o.oprleft = l.oid)
inner join pg_type r on (o.oprright = r.oid)
where oprname = '||';
typname | typname | oprcode
-------------+-------------+-----------------
anyarray | anyelement | array_append
anyelement | anyarray | array_prepend
anyarray | anyarray | array_cat
text | text | textcat
varbit | varbit | bitcat
bytea | bytea | byteacat
text | anynonarray | textanycat
anynonarray | text | anytextcat
tsvector | tsvector | tsvector_concat
tsquery | tsquery | tsquery_or
(10 rows)
\df+ anytextcat shows that it's stable (I'm running 9.4beta2+git changes, but it's the same back to 9.2).
Looking up the relevant line of pg_proc.h and git blameing it, the last commit was cd30728f, but looking at git show demonstrates that it isn't relevant. So I skip it with git blame cd30728f^ -- ./src/include/catalog/pg_proc.h. Working backwards I see that the changes were:
cd30728f - Allow LEAKPROOF functions for better performance of security views. (9.2)
3db6524f - Mark some more I/O-conversion-invoking functions as stable not volatile. (9.2)
8f9fe6ed - Add notion of a "transform function" that can simplify function calls. (9.2)
c82d931d - Fix the volatility marking of textanycat() and anytextcat() (9.0)
Of those, only c82d931d is really relevant. 3db6524f changes it from volatile to stable, but that doesn't help you.
git show c82d931d describes the commit that likely caused this behaviour change as:
commit c82d931dd180965a9a0c06acc764404f91de8170
Author: Tom Lane <[email protected]>
Date: Thu May 27 16:20:11 2010 +0000
Fix the volatility marking of textanycat() and anytextcat(): they were marked
immutable, but that is wrong in general because the cast from the polymorphic
argument to text could be stable or even volatile. Mark them volatile for
safety. In the typical case where the cast isn't volatile, the planner will
deduce the correct expression volatility after inlining the function, so
performance is not lost. The just-committed fix in CREATE INDEX also ensures
this won't break any indexing cases that ought to be allowed.
Per discussion, I'm not bumping catversion for this change, as it doesn't
seem critical enough to force an initdb on beta testers.
According to:
git branch --contains c82d931d
it was introduced in the PostgreSQL 9.0.0 release.
The commit message suggests that it's supposed not to cause any issues with indexes that're safe, and the later change shouldn't make any difference there.
However, looking closer, you're not using text or varchar. You're using the horrible old char type, which is internally bpchar in PostgreSQL.
The issue here will be either that there's a subtle behaviour with char that's locale dependent, or that an oversight caused this change not to handle the case for char. I'd need to dig much deeper into the sources than I have time for tonight to be able to be sure, and frankly for the char(n) type I don't really care to.
You should probably just use varchar(5). If you need space padding, consider doing it with lpad.
Otherwise, please report this issue to pgsql-bugs - but make sure you show the exact versions, from SELECT version().
create index on test (kuupaev, kellaaeg);SELECT max( kuupaev||kellaaeg ) as res from test where kuupaev <=date'20140101' and kuupaev||kellaaeg <= '2014010114 23'Will your index speed it ?whereclause?where kuupaev <= date '2014-01-01' and keellag < '23'. This concatenation doesn't really make sense.14as part of thekeelaavcolumn.where kuupaev <= date '2014-01-01' and keellag <= '14 23'should be equivalent. The comparison<= '14 23'seems strange - but that was the same with your initial query. I'm tempted to say that there is something wrong with your data model.