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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
