vladiksun commented on issue #1000:
URL: https://github.com/apache/age/issues/1000#issuecomment-1680176935
@jrgemignani indexes work fine for cypher equality operator but we were not
able to use the index for Cypher IN operator.
For example:
```
ALTER FUNCTION agtype_in_operator IMMUTABLE;
CREATE INDEX profile_pet_btree_check_in_operator ON test_graph."profile"
USING BTREE (
ag_catalog.agtype_in_operator('["dog", "cat"]'::agtype,
agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id,
_label_name('17580'::oid, id), properties), '"pet"'::agtype]))
);
```
The query:
```
select
any_profile
from ag_catalog.cypher('test_graph',$$
EXPLAIN ANALYZE
MATCH (any_profile:`profile` { hidden: false })
WHERE any_profile.pet IN ['dog', 'cat']
RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);
```
For some reason the optimizer does not involve the index even if we try to
multiple data ten times.
Is it safe to change the volatility flag for ``` agtype_in_operator ```
function ?
| QUERY PLAN |
| :--- |
| Gather \(cost=1000.00..11311.91 rows=133 width=32\) \(actual
time=0.616..345.966 rows=133994 loops=1\) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Parallel Seq Scan on profile any\_profile \(cost=0.00..10298.61
rows=55 width=32\) \(actual time=0.254..331.683 rows=44665 loops=3\) |
| Filter: \(\(properties @> agtype\_build\_map\('hidden'::text,
'false'::agtype\)\) AND agtype\_in\_operator\('\["dog", "cat"\]'::agtype,
agtype\_access\_operator\(VARIADIC ARRAY\[\_agtype\_build\_vertex\(id,
\_label\_name\('17580'::oid, id\), properties\), '"pet"'::agtype\]\)\)\) |
| Rows Removed by Filter: 88669 |
| Planning Time: 0.295 ms |
| Execution Time: 350.271 ms |
--
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]
