pdpotter commented on issue #1009:
URL: https://github.com/apache/age/issues/1009#issuecomment-1850533872
I tried the latest code in the master branch. Unfortunately, indexes on
specific property indexes still aren't used in my reproduction (both on a
numeric property as on a textual property).
Reproduction:
* Run latest master branch code in docker container
```sh
git clone g...@github.com:apache/age.git
cd age
docker build -f docker/Dockerfile . -t age_index
docker run -e POSTGRES_USER=age_index -e POSTGRES_PASSWORD=age_index -e
POSTGRES_DB=age_index -p 5413:5432 age_index
```
* Connect with this server (other shell)
```sh
psql -h 127.0.0.1 -p 5413 -U age_index -d age_index
```
* Try to get specific indexes working
```sql
load 'age';
# LOAD
SET search_path = ag_catalog, "$user", public;
# SET
SELECT create_graph('cypher_index');
# NOTICE: graph "cypher_index" has been created
# create_graph
#--------------
#
#(1 row)
#
SELECT create_vlabel('cypher_index', 'Movie');
#NOTICE: VLabel "Movie" has been created
# create_vlabel
#---------------
#
#(1 row)
#
CREATE INDEX id ON cypher_index."Movie" USING gin ((properties->'id'::text));
#CREATE INDEX
CREATE INDEX imdb ON cypher_index."Movie" USING gin
((properties->'imdb'::text));
#CREATE INDEX
SELECT * from cypher('cypher_index', $$
CREATE
(:Movie {id: 1, name: 'The Shawshank Redemption', imdb : 'tt0111161'}),
(:Movie {id: 2, name: 'The Godfather', imdb : 'tt0068646'}),
(:Movie {id: 3, name: 'The Dark Knight', imdb : 'tt0468569'})
$$) as (V agtype);
# v
#---
#(0 rows)
#
SET enable_seqscan = false;
# SET
SELECT * FROM cypher('cypher_index', $$ MATCH(n:Movie {id: 1}) return n $$)
AS (a agtype);
# a
#-------------------------------------------------------------------------------------------------------------------------------------
# {"id": 844424930131969, "label": "Movie", "properties": {"id": 1, "imdb":
"tt0111161", "name": "The Shawshank Redemption"}}::vertex
#(1 row)
#
SELECT * FROM cypher('cypher_index', $$ EXPLAIN ANALYZE MATCH(n:Movie {id:
1}) return n $$) AS (a agtype);
# QUERY PLAN
#--------------------------------------------------------------------------------------------------------------------------
# Seq Scan on "Movie" n (cost=10000000000.00..10000000025.00 rows=1
width=32) (actual time=49.585..49.589 rows=1 loops=1)
# Filter: (properties @> '{"id": 1}'::agtype)
# Rows Removed by Filter: 2
# Planning Time: 0.123 ms
# JIT:
# Functions: 4
# Options: Inlining true, Optimization true, Expressions true, Deforming
true
# Timing: Generation 0.507 ms, Inlining 17.602 ms, Optimization 27.453 ms,
Emission 4.498 ms, Total 50.060 ms
# Execution Time: 50.140 ms
#(9 rows)
#
SELECT * FROM cypher('cypher_index', $$ EXPLAIN ANALYZE MATCH(n:Movie) where
n.id = 1 return n $$) AS (a agtype);
#QUERY PLAN
#--------------------------------------------------------------------------------------------------------------------------
# Seq Scan on "Movie" n (cost=10000000000.00..10000000028.03 rows=6
width=32) (actual time=47.709..47.713 rows=1 loops=1)
# Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"id"'::agtype]) = '1'::agtype)
# Rows Removed by Filter: 2
# Planning Time: 0.114 ms
# JIT:
# Functions: 4
# Options: Inlining true, Optimization true, Expressions true, Deforming
true
# Timing: Generation 0.493 ms, Inlining 17.772 ms, Optimization 24.743 ms,
Emission 5.166 ms, Total 48.174 ms
# Execution Time: 48.304 ms
#(9 rows)
#
SELECT * FROM cypher('cypher_index', $$ EXPLAIN ANALYZE MATCH(n:Movie {imdb:
'tt0111161'}) return n $$) AS (a agtype);
#QUERY PLAN
#--------------------------------------------------------------------------------------------------------------------------
# Seq Scan on "Movie" n (cost=10000000000.00..10000000025.00 rows=1
width=32) (actual time=50.243..50.248 rows=1 loops=1)
# Filter: (properties @> '{"imdb": "tt0111161"}'::agtype)
# Rows Removed by Filter: 2
# Planning Time: 0.133 ms
# JIT:
# Functions: 4
# Options: Inlining true, Optimization true, Expressions true, Deforming
true
# Timing: Generation 0.473 ms, Inlining 18.220 ms, Optimization 27.395 ms,
Emission 4.596 ms, Total 50.684 ms
# Execution Time: 50.766 ms
#(9 rows)
#
SELECT * FROM cypher('cypher_index', $$ EXPLAIN ANALYZE MATCH(n:Movie) where
n.imdb = 'tt0111161' return n $$) AS (a agtype);
#QUERY PLAN
#--------------------------------------------------------------------------------------------------------------------------
# Seq Scan on "Movie" n (cost=10000000000.00..10000000028.03 rows=6
width=32) (actual time=40.654..40.662 rows=1 loops=1)
# Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"imdb"'::agtype]) = '"tt0111161"'::agtype)
# Rows Removed by Filter: 2
# Planning Time: 0.119 ms
# JIT:
# Functions: 4
# Options: Inlining true, Optimization true, Expressions true, Deforming
true
# Timing: Generation 0.449 ms, Inlining 13.580 ms, Optimization 21.965 ms,
Emission 5.067 ms, Total 41.061 ms
# Execution Time: 41.163 ms
#(9 rows)
#
```
Do the specific property indexes need to be constructed differently?
--
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
