xuan2orange commented on issue #2137:
URL: https://github.com/apache/age/issues/2137#issuecomment-2522456937
> @pritish-moharir
>
> You have to create index on the expression used by age to access a certain
property.
>
> ```
> issue_2137=# SELECT * FROM cypher('test', $$EXPLAIN (costs off) MATCH
(n1:NodeType1)
> WHERE n1.name = 'node1'
> RETURN n1
> $$) AS (result agtype);
> QUERY PLAN
>
------------------------------------------------------------------------------------------------------
> Seq Scan on "NodeType1" n1
> Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype]) = '"node1"'::agtype)
> (2 rows)
> ```
>
> ```
> issue_2137=# CREATE INDEX idx_btree_name
> ON test."NodeType1"
> USING btree (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype]));
> CREATE INDEX
> ```
>
> ```
> issue_2137=# SELECT * FROM cypher('test', $$EXPLAIN (costs off) MATCH
(n1:NodeType1)
> WHERE n1.name = 'node1'
> RETURN n1
> $$) AS (result agtype);
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------
> Index Scan using idx_btree_name on "NodeType1" n1
> Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype]) = '"node1"'::agtype)
> (2 rows)
> ```
>
> If you want to utilize gin index, you need to use the filter like `MATCH
(n {att1: 'value1'})`, since containment operator in where clause is not
supported as of now. Below is an example:
>
> ```
> issue_2137=# CREATE INDEX idx_gin
> ON test."NodeType1"
> USING gin (properties);
> CREATE INDEX
> ```
>
> ```
> issue_2137=# SELECT * FROM cypher('test', $$EXPLAIN (costs off) MATCH
(n1:NodeType1 {name: "Node1"})
> RETURN n1
> $$) AS (result agtype);
> QUERY PLAN
> -----------------------------------------------------------------
> Bitmap Heap Scan on "NodeType1" n1
> Recheck Cond: (properties @> '{"name": "Node1"}'::agtype)
> -> Bitmap Index Scan on idx_gin
> Index Cond: (properties @> '{"name": "Node1"}'::agtype)
> (4 rows)
> ```
>
> Also, querying undirected paths can really slow down the performance, so
consider using directed paths in MATCH clause wherever possible. I hope this
helps.
I have created the btree index and it works.
But when i use 'LIMIT 500' to filter data, it doesn't work.
Here is my query:
`create index node_name_idx on graph_name."MyNode"
(ag_catalog.agtype_access_operator(properties,'"name"'));`
`EXPLAIN ANALYZE SELECT *
FROM cypher('graph_name', $$
MATCH (v:MyNode)
WHERE v.name = 'test'
RETURN v
SKIP 0
LIMIT 500
$$) as (V agtype);`
`Limit (cost=0.00..442.63 rows=500 width=32) (actual time=965.468..970.870
rows=500 loops=1)
-> Seq Scan on "MyNode" "v" (cost=0.00..50239.95 rows=56751 width=32)
(actual time=965.465..970.816 rows=500 loops=1)
Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"name"'::agtype]) = '"test"'::agtype)
Rows Removed by Filter: 326964
Planning Time: 0.280 ms
Execution Time: 970.948 ms`
Thanks!
--
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
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
