vladiksun opened a new issue, #1000: URL: https://github.com/apache/age/issues/1000
**Describe the bug** Index is not used in the WHERE clause. **How are you accessing AGE (Command line, driver, etc.)?** - JDBC **What data setup do we need to do?** ```pgsql LOAD 'age'; SET search_path = ag_catalog, "$user", public; select ag_catalog.create_graph('test_graph'); select ag_catalog.create_vlabel('test_graph','profile'); -- works only for MATCH because GIN only makes sense for pattern matching what MATCH clause is CREATE INDEX profile_gin_idx ON test_graph."profile" USING GIN (properties); -- does not work in the where clause CREATE INDEX profile_pet_btree_idx1 ON test_graph."profile" USING BTREE ((properties -> 'pet')); -- does not work in the where clause CREATE INDEX profile_pet_btree_idx2 ON test_graph."profile" USING BTREE (ag_catalog.agtype_access_operator(properties, '"pet"'::ag_catalog.agtype)); ------ generate more data ------- DO $do$ BEGIN FOR i IN 1..10000 LOOP EXECUTE format(' select * from ag_catalog.cypher(''test_graph'', $$ CREATE (any_vertex: profile { `id`: "%s", `pet`: "%s", `hidden`: %s }) RETURN any_vertex $$ ) as (any_vertex ag_catalog.agtype)', (SELECT uuid_in(md5(random()::text || now()::text)::cstring)), (SELECT ('[0:2]={dog,cat,bird}'::text[])[floor(random()*3)]), (SELECT ('[0:1]={true,false}'::text[])[floor(random()*2)]) ); END LOOP; END $do$; ``` **What is the command that caused the error?** ```pgsql select any_profile from ag_catalog.cypher('test_graph',$$ EXPLAIN ANALYZE MATCH (any_profile:`profile` { hidden: false }) WHERE any_profile.pet = 'dog' RETURN any_profile $$ ) as (any_profile ag_catalog.agtype); ``` | QUERY PLAN | | :--- | | Bitmap Heap Scan on profile any\_profile \(cost=20.08..52.40 rows=1 width=32\) \(actual time=1.111..22.752 rows=1616 loops=1\) | | Recheck Cond: \(properties @> agtype\_build\_map\('hidden'::text, 'false'::agtype\)\) | | Filter: \(agtype\_access\_operator\(VARIADIC ARRAY\[\_agtype\_build\_vertex\(id, \_label\_name\('17463'::oid, id\), properties\), '"pet"'::agtype\]\) = '"dog"'::agtype\) | | Rows Removed by Filter: 3384 | | Heap Blocks: exact=143 | | -> Bitmap Index Scan on profile\_gin\_idx \(cost=0.00..20.08 rows=10 width=0\) \(actual time=1.010..1.010 rows=5000 loops=1\) | | Index Cond: \(properties @> agtype\_build\_map\('hidden'::text, 'false'::agtype\)\) | | Planning Time: 0.733 ms | | Execution Time: 22.869 ms | **Expected behavior** Either of two indexes should be used **Environment (please complete the following information):** - Version: [e.g. 1.3.0] **Additional context** As we see from the plan the filter function is applied as: \ `Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('17463'::oid, id), properties), '"pet"'::agtype]) = '"dog"'::agtype)` We also tried to simulate the functional index like this with no luck because there is a mutable function involved: \ `CREATE INDEX profile_pet_btree_idx3 ON test_graph."profile" USING BTREE ( ag_catalog.agtype_access_operator(VARIADIC ARRAY[age_properties(_agtype_build_vertex(id, _label_name('16945'::oid, id), properties)), '"pet"'::ag_catalog.agtype]) );` Could it be possible to add an index support for the WHERE clause at least via any functional index by not involving mutable functions ? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@age.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org