jrgemignani commented on issue #562:
URL: https://github.com/apache/age/issues/562#issuecomment-1692210640
@endriux @vladiksun I've looked into this and found a few things worth
pointing out. I will mention that I am no expert on indexes. What follows is
from my understanding of the code underneath and how these commands are
processed. And yes, as I learn and debug more of it, that can change.
It is also worth pointing out that Cypher is its own language and some
things are not directly translatable to SQL and vice versa. Just because
something is possible or done a particular way in SQL doesn't necessarily
translate to it being possible or done that way in Cypher. Just stating the
obvious, just in case.
In the following MATCH command the two pattern matches are not the same. One
is part of the MATCH clause, the other is part of the WHERE clause. The MATCH
selects stuff based on its criteria and then passes it to the WHERE which then
filters out stuff based on its criteria.
`MATCH (u: {key: value}) WHERE u.key = value RETURN u`
When the indexes are applied, they are tested against the clauses, one at a
time, from my understanding. When matching an index to a specific clause, the
components need to match. If you look at the MATCH above, while the pattern
matches are similar, they are not the same. One is done in the MATCH, the other
in the WHERE. MATCH works on a path, WHERE works on an expression. An index for
one won't work for the other.
Comparing the two MATCH commands that you listed, shown below, they also are
not the same. One WHERE is for the id function on v, the other is for the
access operator function on v. You can see this in the EXPLAIN outputs later on.
```
MATCH (v:Visit) where id(v) = 9570149208162305 RETURN v
MATCH (v:Visit) where v.Id = "2f10d37f58b51ffd47777bd988194be7" RETURN v
```
What this means is that you would need to create an index for each of these
WHERE clauses, which is now possible to do.
```
psql-15.4-5432-pgsql=# CREATE INDEX btree_check_id_func ON graph."Visit"
USING BTREE (age_id(_agtype_build_vertex(id, _label_name('16955'::oid, id),
properties)));
CREATE INDEX
psql-15.4-5432-pgsql=#
psql-15.4-5432-pgsql=# CREATE INDEX btree_check_id ON graph."Visit" USING
BTREE (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id,
_label_name('16955'::oid, id), properties), '"Id"'::agtype]));
CREATE INDEX
psql-15.4-5432-pgsql=#
```
Now both of your queries will run using those indexes.
```
psql-15.4-5432-pgsql=# select * from cypher('graph', $$ explain analyze
MATCH (v:Visit) where id(v) = 9570149208162305 RETURN v $$) as (a agtype);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on "Visit" v (cost=139.18..11138.88 rows=5000 width=32)
(actual time=0.078..0.080 rows=0 loops=1)
Recheck Cond: (age_id(_agtype_build_vertex(id, _label_name('16955'::oid,
id), properties)) = '9570149208162305'::agtype)
-> Bitmap Index Scan on btree_check_id_func (cost=0.00..137.93
rows=5000 width=0) (actual time=0.073..0.074 rows=0 loops=1)
Index Cond: (age_id(_agtype_build_vertex(id,
_label_name('16955'::oid, id), properties)) = '9570149208162305'::agtype)
Planning Time: 0.575 ms
Execution Time: 0.196 ms
(6 rows)
psql-15.4-5432-pgsql=# select * from cypher('graph', $$ explain analyze
MATCH (v:Visit) where v.Id = "2f10d37f58b51ffd47777bd988194be7" RETURN v $$) as
(a agtype);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------
Bitmap Heap Scan on "Visit" v (cost=207.18..11206.88 rows=5000 width=32)
(actual time=0.129..0.131 rows=0 loops=1)
Recheck Cond: (agtype_access_operator(VARIADIC
ARRAY[_agtype_build_vertex(id, _label_name('16955'::oid, id), properties),
'"Id"':
:agtype]) = '"2f10d37f58b51ffd47777bd988194be7"'::agtype)
-> Bitmap Index Scan on btree_check_id (cost=0.00..205.93 rows=5000
width=0) (actual time=0.123..0.124 rows=0 loops=1)
Index Cond: (agtype_access_operator(VARIADIC
ARRAY[_agtype_build_vertex(id, _label_name('16955'::oid, id), properties), '"I
d"'::agtype]) = '"2f10d37f58b51ffd47777bd988194be7"'::agtype)
Planning Time: 0.527 ms
Execution Time: 0.256 ms
(6 rows)
psql-15.4-5432-pgsql=#
```
Hopefully, this is helps to clarify the indexes a bit. Again, this is based
on debugging the index matching code.
--
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
