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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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.htmlPossibly 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.
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs