[Bitnine Tech] - It is not applied property index according to position of condition

[Hyundong-Seo]

Hello, I'm Hyundong working in Bitnine, Korea.

When I execute SELECT clause with condition, It is not applied property index according to position of condition. When the condition is located in WHERE clause, it doesn't work index scan, just full scan. But when it is located in vertex, like photo, it works index scan.

I tested in this environment(256GB - server memory)

• shared_buffers = 64GB
• temp_buffers = 16GB
• work_mem = 512MB
• maintenance_work_mem = 8GB

I tested using queries like the bottom.

SELECT load_labels_from_file('test', 'comment', '~/ldbc_data/comment_1.csv'); CREATE INDEX test_comment_id_idx ON test.comment USING gin(properties); SELECT FROM cypher('test', $$ EXPLAIN ANALYZE MATCH (v:comment {content: 'thanks'}) RETURN v $$) AS (v agtype); SELECT FROM cypher('test', $$ EXPLAIN ANALYZE MATCH (v:comment) where v.content = 'thanks' RETURN v $$) AS (v agtype);

Using data file comment_1.csv

[pdpotter]

GIN indices are not usabale in the WHERE clause (see https://github.com/apache/age/issues/45#issuecomment-1121493128).

[Hyundong-Seo]

@pdpotter I tried the method in the link you sent, but the index is not applied.(#45)

select create_vlabel('test', 'comment');
CREATE UNIQUE INDEX test_comment_id_idx ON test.comment (properties);

SELECT * FROM cypher('test', $$ EXPLAIN ANALYZE MATCH (v:comment {content: 'thanks'}) RETURN v $$) AS (v agtype);
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on comment v  (cost=0.00..98778.79 rows=2052 width=32) (actual time=0.118..2765.764 rows=85559 loops=1)
   Filter: (properties @> agtype_build_map('content'::text, '"thanks"'::agtype))
   Rows Removed by Filter: 1966610
 Planning Time: 0.642 ms
 Execution Time: 2773.867 ms
(5 rows)

SELECT * FROM cypher('test', $$ EXPLAIN ANALYZE MATCH (v:comment) where v.content = 'thanks' RETURN v $$) AS (v agtype);
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on comment v  (cost=0.00..109080.68 rows=10261 width=32) (actual time=0.414..9792.321 rows=85559 loops=1)
   Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('25129'::oid, id), properties), '"content"'::agtype]) = '"thanks"'::agtype)
   Rows Removed by Filter: 1966610
 Planning Time: 0.130 ms
 Execution Time: 9802.126 ms
(5 rows)

Could you tell me how to execute it works in the WHERE clause as well?

[pdpotter]

It is currently impossible to make it work in the WHERE clause.

I think there might b a small issue in the code in your previous comment to make the index work in the MATCH clause: I think

CREATE UNIQUE INDEX test_comment_id_idx ON test.comment (properties);

should be

CREATE UNIQUE INDEX test_comment_id_idx ON test.comment USING gin (properties);

[Hyundong-Seo]

Thank you for your reply. I have one more question, how do I use conditions such as range search using an inequality sign or like search with INDEX SCAN?