BUG #12644: Planner fails to use available index with anything other than default operator - Mailing list pgsql-bugs

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.