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

The following bug has been logged on the website:

Bug reference:      12644
Logged by:          Jim McDonald
Email address:      Jim@mcdee.net
PostgreSQL version: 9.4.0
Operating system:   OSX 10.10.1 Darwin Kernel Version 14.0.0: Fri Sep
Description:

Starting with a table holding a single JSONB value and some test data:

    CREATE TABLE jsonthings(d JSONB NOT NULL);
    INSERT INTO jsonthings VALUES ('{"name":"First","tags":["foo"]}');
    INSERT INTO jsonthings VALUES
('{"name":"Second","tags":["foo","bar"]}');
    INSERT INTO jsonthings VALUES
('{"name":"Third","tags":["bar","baz"]}');
    INSERT INTO jsonthings VALUES ('{"name":"Fourth","tags":["baz"]}');
    CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

Using psql I can run a simple select and it uses the index as expected:

    set enable_seqscan=off;
    EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

                                                                QUERY PLAN



----------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on jsonthings  (cost=113.50..30236.13 rows=10000
width=61) (actual time=0.024..0.025 rows=1 loops=1)
       Recheck Cond: ((d -> 'name'::text) ? 'First'::text)
       Heap Blocks: exact=1
       ->  Bitmap Index Scan on idx_jsonthings_name  (cost=0.00..111.00
rows=10000 width=0) (actual time=0.015..0.015 rows=1 loops=1)
             Index Cond: ((d -> 'name'::text) ? 'First'::text)
     Planning time: 0.073 ms
     Execution time: 0.047 ms
    (7 rows)

However I cannot use the '?' operator because I'm accessing the database
through JDBC and there is no way to escape the '?' character.  Instead I
attempted to use the functino which underpins the '?' operator, however it
is not using the index:

    set enable_seqscan=off;
    EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE
jsonb_exists(d->'name','First');
                                                                QUERY PLAN



----------------------------------------------------------------------------------------------------------------------------------
     Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06
rows=3333334 width=61) (actual time=0.016..3135.119 rows=1 loops=1)
       Filter: jsonb_exists((d -> 'name'::text), 'First'::text)
       Rows Removed by Filter: 10000003
     Planning time: 0.051 ms
     Execution time: 3135.138 ms
    (5 rows)

I also tried to create a custom operator which has the same parameters as
the '?' operator:

    CREATE OPERATOR ### (
      PROCEDURE = jsonb_exists,
      LEFTARG = jsonb,
      RIGHTARG = text,
      RESTRICT = contsel,
      JOIN = contjoinsel);

But that has the same problem:

    set enable_seqscan=off;
    EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ### 'First';
                                                               QUERY PLAN



--------------------------------------------------------------------------------------------------------------------------------
     Seq Scan on jsonthings  (cost=10000000000.00..10000263637.06 rows=10000
width=61) (actual time=0.012..3381.608 rows=1 loops=1)
       Filter: ((d -> 'name'::text) ### 'First'::text)
       Rows Removed by Filter: 10000003
     Planning time: 0.046 ms
     Execution time: 3381.623 ms
    (5 rows)

It appears that the planner is failing to use the index which should work
for both jsonb_exists() and the custom operator.

Jim@mcdee.net writes:
> Using psql I can run a simple select and it uses the index as expected:
>     EXPLAIN ANALYZE SELECT d FROM jsonthings WHERE d->'name' ? 'First';

> 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.)

> 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.

            regards, tom lane

Jim McDonald <Jim@mcdee.net> writes:
> On 24/01/2015 15:01, Tom Lane wrote:
>> 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

> 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?

Not really, I'm afraid.  In the abstract maybe you could add such an
operator to an existing operator class; but in practice this will fall
foul of the unique indexes on pg_amop, which insist that there be
at most one operator per strategy per opclass.

However, there's more than one way to skin a cat.  I think you could
probably define such an operator that references an inlineable SQL
function that expands to the desired underlying operator, along the
lines of

create function my_jsonb_exists(jsonb, text) returns bool as
'select $1 ? $2' language sql immutable;

create operator ### ( procedure = my_jsonb_exists, ...

            regards, tom lane

On 24/01/2015 15:01, Tom Lane wrote:
> Jim@mcdee.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.