You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "alteck45 (via GitHub)" <gi...@apache.org> on 2024/01/25 13:40:13 UTC
[I] Sorted queries do not utilise indices, when such are introduced [age]
alteck45 opened a new issue, #1522:
URL: https://github.com/apache/age/issues/1522
We have been experimenting recently with Apache AGE specifically with the main focus on performance in comparison to the standard Postgres DB.
Our very basic data model contains single vertex label `AccessPoint`. For the simplicity sake the only relevant property on `AccessPoint` label is the `id` value, which is also supposed to be frequently used by the application queries.
The dataset contains 50_013 `AccessPoint` vertexes.
We introduced several indices to see, which are actually being used by the queries of different type. For the completeness here are the indices we introduced on the `AccessPoint` table:
```sql
CREATE UNIQUE INDEX ap_id ON "test-graph"."AccessPoint" USING btree (agtype_access_operator(properties, '"id"'));
CREATE INDEX ap_gin_properties ON "test-graph"."AccessPoint" USING GIN(properties);
CREATE UNIQUE INDEX ap_v_id ON "dcore-graph"."AccessPoint"(id);
```
To sum up, we have BTREE indices on the `properties -> id` and on the vertex id, and we have a GIN index on `properties`.
The problem we have observed is the lack of utilisation of any indices, for a simple query like:
```sql
SELECT * FROM cypher(
'test-graph', $$
MATCH (ap:AccessPoint)
WITH ap
ORDER BY ap.id
RETURN ap
$$
) as (value agtype)
```
The query plan for the query above is:
```sql
Subquery Scan on _age_default_alias_previous_cypher_clause (cost=5631.49..9308.86 rows=29419 width=32) (actual time=1703.577..2113.010 rows=50013 loops=1)
-> Gather Merge (cost=5631.49..9014.67 rows=29419 width=64) (actual time=1703.567..2085.553 rows=50013 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=4631.48..4705.03 rows=29419 width=64) (actual time=1533.703..1700.085 rows=25006 loops=2)
Sort Key: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(ap.id, _label_name('20505'::oid, ap.id), ap.properties), '""id""'::agtype]))"
Sort Method: external merge Disk: 10848kB
Worker 0: Sort Method: external merge Disk: 8512kB
-> Parallel Seq Scan on ""AccessPoint"" ap (cost=0.00..2447.93 rows=29419 width=64) (actual time=2.513..560.890 rows=25006 loops=2)"
Planning Time: 7.458 ms
Execution Time: 2128.705 ms
```
For the comparison a standard SQL query:
```sql
SELECT *
FROM "test-graph"."AccessPoint"
ORDER BY agtype_access_operator(properties, '"id"');
```
utilizes the `ap_id` btree index on `properties -> id`, the query plan:
```sql
Index Scan using ap_id on "AccessPoint" (cost=0.41..9687.64 rows=50013 width=288) (actual time=0.610..347.862 rows=50013 loops=1)
Planning Time: 1.683 ms
Execution Time: 364.441 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: dev-unsubscribe@age.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "sir-sa (via GitHub)" <gi...@apache.org>.
sir-sa commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-2033535189
--Cypher query: Retriev all AccessPoint vertices ordered by id, utilizing BTREE index on vertex id-----
SELECT * FROM cypher(
'test-graph', $$
MATCH (ap:AccessPoint)
WITH ap
ORDER BY id(ap)
RETURN ap
$$
) as (value agtype);
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-2143638874
This issue was closed because it has been stalled for further 14 days with no activity.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1912891494
@alteck45 Could you provide a simple sample set?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1910732993
@alteck45 What version of PostgreSQL and version of AGE are you running this on?
Additionally, these 2 issues covered a lot about indexes, albeit with WHERE, that might be of assistance.
#1235 #1000
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1958148200
@alteck45 Could you provide a simple sample set so that we can try to reproduce this?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1958485554
@alteck45 I'm also a bit confused by `ap.id` what `id` are you referring to or expecting? An `id` that **you** set in the properties or the `id` of the vertex that is generated when the vertex is created?
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "alteck45 (via GitHub)" <gi...@apache.org>.
alteck45 commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1911645755
@jrgemignani PostgreSQL version 16.1 and AGE version 1.5.0
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1960352971
@alteck45 I'm not sure why it is expanding the query out with `ap.id` and `ap.properties`. I have a feeling this is not allowing some of the indexes to work. I will look at the GIN indexes next.
```
psql-16.1-5432-pgsql=# SELECT * FROM cypher(
'test-graph', $$ EXPLAIN
MATCH (ap:AccessPoint)
RETURN ap ORDER BY id(ap)
$$) as (value agtype);
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Subquery Scan on _ (cost=1.49..1.64 rows=12 width=32)
-> Sort (cost=1.49..1.52 rows=12 width=64)
Sort Key: (age_id(_agtype_build_vertex(ap.id, _label_name('16979'::oid, ap.id), ap.properties)))
-> Seq Scan on "AccessPoint" ap (cost=0.00..1.27 rows=12 width=64)
(4 rows)
psql-16.1-5432-pgsql=#
```
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-1958474851
@alteck45 This index won't be used -
`CREATE UNIQUE INDEX ap_id ON "test-graph"."AccessPoint" USING btree (agtype_access_operator(properties, '"id"'));`
due to the query not matching `agtype_access_operator(properties, '"id"')` -
```
(agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(ap.id, _label_name('20505'::oid, ap.id), ap.properties), '""id""'::agtype]))
```
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #1522:
URL: https://github.com/apache/age/issues/1522#issuecomment-2119036119
This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Sorted queries do not utilise indices, when such are introduced [age]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #1522: Sorted queries do not utilise indices, when such are introduced
URL: https://github.com/apache/age/issues/1522
--
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-unsubscribe@age.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org