Обсуждение: BUG #12644: Planner fails to use available index with anything other than default operator
BUG #12644: Planner fails to use available index with anything other than default operator
От
Jim@mcdee.net
Дата:
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.