This is an automated email from the ASF dual-hosted git repository.
mtaha pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/master by this push:
new 7beb6533 Fix and improve index.sql regression test coverage (#2300)
7beb6533 is described below
commit 7beb653303529b05391667de4204ffb4da318eeb
Author: John Gemignani <[email protected]>
AuthorDate: Fri Jan 9 12:55:36 2026 -0800
Fix and improve index.sql regression test coverage (#2300)
NOTE: This PR was created with AI tools and a human.
- Remove unused copy command (leftover from deleted agload_test_graph test)
- Replace broken Section 4 that referenced non-existent graph with
comprehensive WHERE clause tests covering string, int, bool, and float
properties with AND/OR/NOT operators
- Add EXPLAIN tests to verify index usage:
- Section 3: Validate GIN indices (load_city_gin_idx,
load_country_gin_idx)
show Bitmap Index Scan for property matching
- Section 4: Validate all expression indices (city_country_code_idx,
city_id_idx, city_west_coast_idx, country_life_exp_idx) show Index Scan
for WHERE clause filtering
All indices now have EXPLAIN verification confirming they are used as
expected.
modified: regress/expected/index.out
modified: regress/sql/index.sql
---
regress/expected/index.out | 290 ++++++++++++++++++++++++++++++++++++++++++---
regress/sql/index.sql | 174 +++++++++++++++++++++++++--
2 files changed, 436 insertions(+), 28 deletions(-)
diff --git a/regress/expected/index.out b/regress/expected/index.out
index 3ed7b1c3..9faead66 100644
--- a/regress/expected/index.out
+++ b/regress/expected/index.out
@@ -16,7 +16,6 @@
* specific language governing permissions and limitations
* under the License.
*/
-\! cp -r regress/age_load/data regress/instance/data/age_load
LOAD 'age';
SET search_path TO ag_catalog;
SET enable_mergejoin = ON;
@@ -385,6 +384,19 @@ CREATE INDEX load_city_gin_idx
ON cypher_index."City" USING gin (properties);
CREATE INDEX load_country_gin_idx
ON cypher_index."Country" USING gin (properties);
+-- Verify GIN index is used for City property match
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:City {city_id: 1})
+ RETURN c
+$$) as (plan agtype);
+ QUERY PLAN
+--------------------------------------------------------------
+ Bitmap Heap Scan on "City" c
+ Recheck Cond: (properties @> '{"city_id": 1}'::agtype)
+ -> Bitmap Index Scan on load_city_gin_idx
+ Index Cond: (properties @> '{"city_id": 1}'::agtype)
+(4 rows)
+
SELECT * FROM cypher('cypher_index', $$
MATCH (c:City {city_id: 1})
RETURN c
@@ -418,6 +430,19 @@ $$) as (n agtype);
{"id": 1970324836974597, "label": "City", "properties": {"name": "Vancouver",
"city_id": 5, "west_coast": true, "country_code": "CA"}}::vertex
(4 rows)
+-- Verify GIN index is used for Country property match
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:Country {life_expectancy: 82.05})
+ RETURN c
+$$) as (plan agtype);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Bitmap Heap Scan on "Country" c
+ Recheck Cond: (properties @> '{"life_expectancy": 82.05}'::agtype)
+ -> Bitmap Index Scan on load_country_gin_idx
+ Index Cond: (properties @> '{"life_expectancy": 82.05}'::agtype)
+(4 rows)
+
SELECT * FROM cypher('cypher_index', $$
MATCH (c:Country {life_expectancy: 82.05})
RETURN c
@@ -441,26 +466,259 @@ DROP INDEX cypher_index.load_country_gin_idx;
--
-- Section 4: Index use with WHERE clause
--
-SELECT COUNT(*) FROM cypher('cypher_index', $$
+-- Create expression index on country_code property
+CREATE INDEX city_country_code_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"country_code"'::agtype));
+-- Verify index is used with EXPLAIN (should show Index Scan on
city_country_code_idx)
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.country_code = 'US'
+ RETURN a
+$$) as (plan agtype);
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Index Scan using city_country_code_idx on "City" a
+ Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"country_code"'::agtype]) = '"US"'::agtype)
+(2 rows)
+
+-- Test WHERE with indexed string property
+SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
- WHERE a.country_code = 'RS'
+ WHERE a.country_code = 'US'
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-----------------
+ "New York"
+ "San Fransisco"
+ "Los Angeles"
+ "Seattle"
+(4 rows)
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'CA'
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-------------
+ "Vancouver"
+ "Toronto"
+ "Montreal"
+(3 rows)
+
+-- Test WHERE with no matching results
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'XX'
+ RETURN a.name
+$$) as (name agtype);
+ name
+------
+(0 rows)
+
+-- Create expression index on city_id property
+CREATE INDEX city_id_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"city_id"'::agtype));
+-- Verify index is used with EXPLAIN for integer property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.city_id = 1
RETURN a
-$$) as (n agtype);
- count
--------
- 0
+$$) as (plan agtype);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
+ Index Scan using city_id_idx on "City" a
+ Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"city_id"'::agtype]) = '1'::agtype)
+(2 rows)
+
+-- Test WHERE with indexed integer property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id = 1
+ RETURN a.name
+$$) as (name agtype);
+ name
+------------
+ "New York"
(1 row)
-CREATE INDEX CONCURRENTLY cntry_ode_idx ON cypher_index."City"
-(ag_catalog.agtype_access_operator(properties, '"country_code"'::agtype));
-SELECT COUNT(*) FROM cypher('agload_test_graph', $$
+SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
- WHERE a.country_code = 'RS'
+ WHERE a.city_id = 5
+ RETURN a.name
+$$) as (name agtype);
+ name
+-------------
+ "Vancouver"
+(1 row)
+
+-- Test WHERE with comparison operators on indexed property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id < 3
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-----------------
+ "New York"
+ "San Fransisco"
+(2 rows)
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id >= 8
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-------------
+ "Monterrey"
+ "Tijuana"
+(2 rows)
+
+-- Create expression index on west_coast boolean property
+CREATE INDEX city_west_coast_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"west_coast"'::agtype));
+-- Verify index is used with EXPLAIN for boolean property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.west_coast = true
RETURN a
-$$) as (n agtype);
-ERROR: graph "agload_test_graph" does not exist
-LINE 1: SELECT COUNT(*) FROM cypher('agload_test_graph', $$
- ^
+$$) as (plan agtype);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Index Scan using city_west_coast_idx on "City" a
+ Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"west_coast"'::agtype]) = 'true'::agtype)
+(2 rows)
+
+-- Test WHERE with indexed boolean property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.west_coast = true
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-----------------
+ "San Fransisco"
+ "Los Angeles"
+ "Seattle"
+ "Vancouver"
+(4 rows)
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.west_coast = false
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+---------------
+ "New York"
+ "Toronto"
+ "Montreal"
+ "Mexico City"
+ "Monterrey"
+ "Tijuana"
+(6 rows)
+
+-- Test WHERE with multiple conditions (AND)
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'US' AND a.west_coast = true
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-----------------
+ "San Fransisco"
+ "Los Angeles"
+ "Seattle"
+(3 rows)
+
+-- Test WHERE with OR conditions
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id = 1 OR a.city_id = 5
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+ name
+-------------
+ "New York"
+ "Vancouver"
+(2 rows)
+
+-- Test WHERE with NOT
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE NOT a.west_coast = true AND a.country_code = 'US'
+ RETURN a.name
+$$) as (name agtype);
+ name
+------------
+ "New York"
+(1 row)
+
+-- Create expression index on life_expectancy for Country
+CREATE INDEX country_life_exp_idx ON cypher_index."Country"
+(ag_catalog.agtype_access_operator(properties, '"life_expectancy"'::agtype));
+-- Verify index is used with EXPLAIN for float property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:Country)
+ WHERE c.life_expectancy > 80.0
+ RETURN c
+$$) as (plan agtype);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------
+ Index Scan using country_life_exp_idx on "Country" c
+ Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"life_expectancy"'::agtype]) > '80.0'::agtype)
+(2 rows)
+
+-- Test WHERE with float property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (c:Country)
+ WHERE c.life_expectancy > 80.0
+ RETURN c.name
+$$) as (name agtype);
+ name
+----------
+ "Canada"
+(1 row)
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (c:Country)
+ WHERE c.life_expectancy < 76.0
+ RETURN c.name
+$$) as (name agtype);
+ name
+----------
+ "Mexico"
+(1 row)
+
+-- Test WHERE in combination with pattern matching
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (country:Country)<-[:has_city]-(city:City)
+ WHERE country.country_code = 'CA'
+ RETURN city.name
+ ORDER BY city.city_id
+$$) as (name agtype);
+ name
+-------------
+ "Vancouver"
+ "Toronto"
+ "Montreal"
+(3 rows)
+
+-- Clean up indices
+DROP INDEX cypher_index.city_country_code_idx;
+DROP INDEX cypher_index.city_id_idx;
+DROP INDEX cypher_index.city_west_coast_idx;
+DROP INDEX cypher_index.country_life_exp_idx;
--
-- General Cleanup
--
@@ -478,5 +736,3 @@ NOTICE: graph "cypher_index" has been dropped
(1 row)
-SELECT drop_graph('agload_test_graph', true);
-ERROR: graph "agload_test_graph" does not exist
diff --git a/regress/sql/index.sql b/regress/sql/index.sql
index d9a4331a..96e7dd81 100644
--- a/regress/sql/index.sql
+++ b/regress/sql/index.sql
@@ -17,8 +17,6 @@
* under the License.
*/
-\! cp -r regress/age_load/data regress/instance/data/age_load
-
LOAD 'age';
SET search_path TO ag_catalog;
@@ -219,6 +217,11 @@ ON cypher_index."City" USING gin (properties);
CREATE INDEX load_country_gin_idx
ON cypher_index."Country" USING gin (properties);
+-- Verify GIN index is used for City property match
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:City {city_id: 1})
+ RETURN c
+$$) as (plan agtype);
SELECT * FROM cypher('cypher_index', $$
MATCH (c:City {city_id: 1})
@@ -235,6 +238,12 @@ SELECT * FROM cypher('cypher_index', $$
RETURN c
$$) as (n agtype);
+-- Verify GIN index is used for Country property match
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:Country {life_expectancy: 82.05})
+ RETURN c
+$$) as (plan agtype);
+
SELECT * FROM cypher('cypher_index', $$
MATCH (c:Country {life_expectancy: 82.05})
RETURN c
@@ -250,23 +259,166 @@ DROP INDEX cypher_index.load_country_gin_idx;
--
-- Section 4: Index use with WHERE clause
--
-SELECT COUNT(*) FROM cypher('cypher_index', $$
+-- Create expression index on country_code property
+CREATE INDEX city_country_code_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"country_code"'::agtype));
+
+-- Verify index is used with EXPLAIN (should show Index Scan on
city_country_code_idx)
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.country_code = 'US'
+ RETURN a
+$$) as (plan agtype);
+
+-- Test WHERE with indexed string property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'US'
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
- WHERE a.country_code = 'RS'
+ WHERE a.country_code = 'CA'
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+-- Test WHERE with no matching results
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'XX'
+ RETURN a.name
+$$) as (name agtype);
+
+-- Create expression index on city_id property
+CREATE INDEX city_id_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"city_id"'::agtype));
+
+-- Verify index is used with EXPLAIN for integer property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.city_id = 1
RETURN a
-$$) as (n agtype);
+$$) as (plan agtype);
-CREATE INDEX CONCURRENTLY cntry_ode_idx ON cypher_index."City"
-(ag_catalog.agtype_access_operator(properties, '"country_code"'::agtype));
+-- Test WHERE with indexed integer property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id = 1
+ RETURN a.name
+$$) as (name agtype);
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id = 5
+ RETURN a.name
+$$) as (name agtype);
+
+-- Test WHERE with comparison operators on indexed property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id < 3
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
-SELECT COUNT(*) FROM cypher('agload_test_graph', $$
+SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
- WHERE a.country_code = 'RS'
+ WHERE a.city_id >= 8
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+-- Create expression index on west_coast boolean property
+CREATE INDEX city_west_coast_idx ON cypher_index."City"
+(ag_catalog.agtype_access_operator(properties, '"west_coast"'::agtype));
+
+-- Verify index is used with EXPLAIN for boolean property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.west_coast = true
RETURN a
-$$) as (n agtype);
+$$) as (plan agtype);
+
+-- Test WHERE with indexed boolean property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.west_coast = true
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.west_coast = false
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+-- Test WHERE with multiple conditions (AND)
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.country_code = 'US' AND a.west_coast = true
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+-- Test WHERE with OR conditions
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE a.city_id = 1 OR a.city_id = 5
+ RETURN a.name
+ ORDER BY a.city_id
+$$) as (name agtype);
+
+-- Test WHERE with NOT
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (a:City)
+ WHERE NOT a.west_coast = true AND a.country_code = 'US'
+ RETURN a.name
+$$) as (name agtype);
+
+-- Create expression index on life_expectancy for Country
+CREATE INDEX country_life_exp_idx ON cypher_index."Country"
+(ag_catalog.agtype_access_operator(properties, '"life_expectancy"'::agtype));
+
+-- Verify index is used with EXPLAIN for float property
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (c:Country)
+ WHERE c.life_expectancy > 80.0
+ RETURN c
+$$) as (plan agtype);
+
+-- Test WHERE with float property
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (c:Country)
+ WHERE c.life_expectancy > 80.0
+ RETURN c.name
+$$) as (name agtype);
+
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (c:Country)
+ WHERE c.life_expectancy < 76.0
+ RETURN c.name
+$$) as (name agtype);
+
+-- Test WHERE in combination with pattern matching
+SELECT * FROM cypher('cypher_index', $$
+ MATCH (country:Country)<-[:has_city]-(city:City)
+ WHERE country.country_code = 'CA'
+ RETURN city.name
+ ORDER BY city.city_id
+$$) as (name agtype);
+
+-- Clean up indices
+DROP INDEX cypher_index.city_country_code_idx;
+DROP INDEX cypher_index.city_id_idx;
+DROP INDEX cypher_index.city_west_coast_idx;
+DROP INDEX cypher_index.country_life_exp_idx;
--
-- General Cleanup
--
SELECT drop_graph('cypher_index', true);
-SELECT drop_graph('agload_test_graph', true);
