Re: BUG #12644: Planner fails to use available index with anything other than default operator

On 24/01/2015 15:01, Tom Lane wrote:
> Jim(at)mcdee(dot)net writes:
>> However I cannot use the '?' operator because I'm accessing the database
>> through JDBC and there is no way to escape the '?' character.
> Seems to me you need to discuss that problem with the pgsql-jdbc folk.
> (I suspect they have some solution already, because operators whose names
> contain '?' have been around for a very very long time.)
Unfortunately they don't, at least from the last conversations I have
seen regarding this. I'll take a look to see if it's simple enough to
put some sort of escape in place.
>
>> Instead I attempted to use the functino which underpins the '?'
>> operator, however it is not using the index:
> Nope, this doesn't work, never has, and will not in the foreseeable
> future. Index access is defined in terms of operators, not other
> ways to access the same function; see
> http://www.postgresql.org/docs/9.4/static/indexes-opclass.html
>
> Possibly the Berkeley crew should have done it the other way;
> but they didn't, and we're unlikely to try to move that mountain
> now.
Fair enough. As a workaround in the meantime is it possible to create a
custom operator that is recognised as being in the correct family/class
so that it will use the index? I attempted to add an operator '###' as
a synonym for '?' with the JSONB type but it doesn't pick up the index
either:

CREATE OPERATOR ### (
PROCEDURE = jsonb_exists,
LEFTARG = jsonb,
RIGHTARG = text,
RESTRICT = contsel,
JOIN = contjoinsel);
>
> regards, tom lane
Cheers,
Jim.