Pg17.7 Age1.6 the issue of using gin index

[ZhengZhanFei]

On page 17.7, for age1.6, when I was using it, I first created a Gin index, and then inserted data in batches. However, the data could not be retrieved.

CREATE EXTENSION IF NOT EXISTS age;

SET search_path = ag_catalog, "$user", PUBLIC;

SELECT create_graph('graph_name');

SELECT create_vlabel('graph_name','n1');

create index idx_n1_properties_gin on graph_name."n1" using gin(properties)

-- There are 10 pieces of data in the CSV file.
SELECT load_labels_from_file(
'graph_name',
'n1',
'/tmp/age/age-node-10.csv'
);

-- Return 10
SELECT COUNT(*) FROM graph_name."n1";

-- Query Statement 1
-- {"id": 844424930131969, "label": "n1", "properties": {"id": "1", "id": 1, "node_name": "type2_1", "node_type": "type2", "node_identify": "1"}}::vertex
SELECT *
FROM cypher('graph_name', $$
MATCH (a:n1 {node_name: 'type2_1', node_type: 'type2'})
RETURN
a
$$) AS (
a agtype
);

-- Query Statement 2
SELECT *
FROM cypher('graph_name', $$
MATCH (a:n1 {node_name: 'type2_1'})
RETURN
a
$$) AS (
a agtype
);

During the first query, my conditions were 'node_name' and 'node_type', and I was able to retrieve the records. However, during the second query, I removed the 'node_type' condition from my criteria, but no results were returned. I thought that the second query should also have returned results, but it didn't.

-- -- Query Statement 3
SELECT *
FROM cypher('graph_name', $$
MATCH (a:n1)
WHERE a.node_name = 'type2_1'
RETURN a
$$) AS (
a agtype
);

During the third query, I was able to retrieve the information by using the "where" clause.

The execution plans corresponding to the three SQL statements are as follows: when using the gin index, there will be a situation where the record cannot be retrieved.

SELECT *
FROM cypher('graph_name', $$
explain MATCH (a:n1 {node_name: 'type2_1', node_type: 'type2'})
RETURN
a
$$) AS (
a agtype
);
Seq Scan on n1 a (cost=0.00..25.06 rows=12 width=32)
Filter: (properties @> '{"node_name": "type2_1", "node_type": "type2"}'::agtype)

SELECT *
FROM cypher('graph_name', $$
explain MATCH (a:n1 {node_name: 'type2_1'})
RETURN
a
$$) AS (
a agtype
);

Bitmap Heap Scan on n1 a (cost=13.04..23.79 rows=12 width=32)
Recheck Cond: (properties @> '{"node_name": "type2_1"}'::agtype)
-> Bitmap Index Scan on idx_n1_properties_gin (cost=0.00..13.04 rows=12 width=0)
Index Cond: (properties @> '{"node_name": "type2_1"}'::agtype)

SELECT *
FROM cypher('graph_name', $$
explain MATCH (a:n1)
WHERE a.node_name = 'type2_1'
RETURN a
$$) AS (
a agtype
);
Seq Scan on n1 a (cost=0.00..28.03 rows=6 width=32)
Filter: (agtype_access_operator(VARIADIC ARRAY[properties, '"node_name"'::agtype]) = '"type2_1"'::agtype)

My installation process:
sudo apt-get install build-essential libreadline-dev zlib1g-dev flex bison
sudo git clone https://github.com/apache/age.git
cd age
git config --global --add safe.directory /home/age
sudo git checkout PG17/v1.6.0-rc0
sudo make PG_CONFIG=/Programs/postgres17.7/bin/pg_config install

-- postgres version
select version()
PostgreSQL 17.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit

I have been struggling with this for a long time. I want to know where I went wrong. Thank you for your help.

[MuhammadTahaNaveed]

@ZhengZhanFei I was able to reproduce the issue on AGE 1.6.0 but not on the current master branch. See the following:

issue_2322=# CREATE EXTENSION IF NOT EXISTS age;
CREATE EXTENSION
issue_2322=# SET search_path = ag_catalog, "$user", PUBLIC;
SET
issue_2322=# SELECT create_graph('agload_test_graph');
NOTICE:  graph "agload_test_graph" has been created
 create_graph 
--------------
 
(1 row)

issue_2322=# SELECT create_vlabel('agload_test_graph', 'Country');
NOTICE:  VLabel "Country" has been created
 create_vlabel 
---------------
 
(1 row)

issue_2322=# CREATE INDEX ON agload_test_graph."Country" USING GIN (properties);
CREATE INDEX
issue_2322=# SELECT load_labels_from_file('agload_test_graph', 'Country',
    'age_load/data/countries.csv', true);
 load_labels_from_file 
-----------------------
 
(1 row)

issue_2322=# EXPLAIN select * from cypher('agload_test_graph', $$MATCH (n:Country {tld: ".ax"}) return n$$) as (a agtype);
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on "Country" n  (cost=0.00..3.67 rows=1 width=32)
   Filter: (properties @> '{"tld": ".ax"}'::agtype)
(2 rows)

issue_2322=# select * from cypher('agload_test_graph', $$MATCH (n:Country {tld: ".ax"}) return n$$) as (a agtype);
                                                                                                                                                                                              
                            a                                                                                                                                                                 
                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------
 {"id": 844424930131970, "label": "Country", "properties": {"id": "2", "tld": ".ax", "iso2": "AX", "iso3": "ALA", "name": "Aland Islands", "emoji": "🇦🇽", "__id__": 2, "emojiU": "U+1F1E6 U+1F
1FD", "native": "Åland", "region": "Europe", "capital": "Mariehamn", "currency": "EUR", "latitude": "60.116667", "longitude": "19.9", "subregion": "Northern Europe", "phone_code": "+358-18",
 "numeric_code": "248", "currency_symbol": "€"}}::vertex
(1 row)

issue_2322=# set enable_seqscan = off;
SET
issue_2322=# EXPLAIN select * from cypher('agload_test_graph', $$MATCH (n:Country {tld: ".ax"}) return n$$) as (a agtype);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on "Country" n  (cost=12.99..17.01 rows=1 width=32)
   Recheck Cond: (properties @> '{"tld": ".ax"}'::agtype)
   ->  Bitmap Index Scan on "Country_properties_idx"  (cost=0.00..12.99 rows=1 width=0)
         Index Cond: (properties @> '{"tld": ".ax"}'::agtype)
(4 rows)

issue_2322=# select * from cypher('agload_test_graph', $$MATCH (n:Country {tld: ".ax"}) return n$$) as (a agtype);
                                                                                                                                                                                              
                            a                                                                                                                                                                 
                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------
 {"id": 844424930131970, "label": "Country", "properties": {"id": "2", "tld": ".ax", "iso2": "AX", "iso3": "ALA", "name": "Aland Islands", "emoji": "🇦🇽", "__id__": 2, "emojiU": "U+1F1E6 U+1F
1FD", "native": "Åland", "region": "Europe", "capital": "Mariehamn", "currency": "EUR", "latitude": "60.116667", "longitude": "19.9", "subregion": "Northern Europe", "phone_code": "+358-18",
 "numeric_code": "248", "currency_symbol": "€"}}::vertex
(1 row)

AGE 1.7.0 for PG17 will have a fix for this issue and will be out soon, the release cycle has already started. For now, you can use REINDEX INDEX graph.label_name after the load command to rebuild the outdated index and the query should work fine.