rafsun42 commented on issue #1235:
URL: https://github.com/apache/age/issues/1235#issuecomment-1771289898
@vladiksun
Additionally, I would like to introduce a configration parameter I worked
on. It may or may not be useful for you. It let you choose which operator to
use for MATCH's property filter. If you set the parameter OFF,
```sql
SET age.enable_containment = OFF; -- default is ON
```
AGE will use `agtype_access_operator` instead of `@>` for MATCH's property
filter.
It gives you the choice to use BTREE index over GIN index (for MATCH) which
may be preferable sometimes.
```sql
SET age.enable_containment = off;
CREATE INDEX pet_btree
ON test_graph.profile
USING btree (agtype_access_operator(VARIADIC ARRAY[properties,
'"pet"'::agtype]));
CREATE INDEX hidden_btree
ON test_graph.profile
USING btree (agtype_access_operator(VARIADIC ARRAY[properties,
'"hidden"'::agtype]));
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);
```
```txt
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on profile any_profile (cost=14.23..18.26 rows=1
width=32) (actual time=7.281..21.149 rows=3325 loops=1)
Recheck Cond: ((agtype_access_operator(VARIADIC ARRAY[properties,
'"hidden"'::agtype]) = 'false'::agtype) AND (agtype_access_operator(VARIADIC
ARRAY[properties, '"pet"'::agtype]) = ANY ('{"\"dog\"","\"cat\""}'::agtype[])))
Heap Blocks: exact=143
-> BitmapAnd (cost=14.23..14.23 rows=1 width=0) (actual
time=7.124..7.126 rows=0 loops=1)
-> Bitmap Index Scan on hidden_btree (cost=0.00..4.66 rows=50
width=0) (actual time=2.697..2.698 rows=4978 loops=1)
Index Cond: (agtype_access_operator(VARIADIC
ARRAY[properties, '"hidden"'::agtype]) = 'false'::agtype)
-> Bitmap Index Scan on pet_btree (cost=0.00..9.32 rows=100
width=0) (actual time=4.403..4.403 rows=6615 loops=1)
Index Cond: (agtype_access_operator(VARIADIC
ARRAY[properties, '"pet"'::agtype]) = ANY ('{"\"dog\"","\"cat\""}'::agtype[]))
Planning Time: 1.415 ms
Execution Time: 21.735 ms
(10 rows)
```
--
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]
