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 a1f472d6 Fix and improve index.sql addendum (#2301)
a1f472d6 is described below
commit a1f472d6f9344dc4449ac7343bf7d81a31b66f02
Author: John Gemignani <[email protected]>
AuthorDate: Sat Jan 10 08:37:54 2026 -0800
Fix and improve index.sql addendum (#2301)
NOTE: This PR was created with the help of AI tools and a human.
Added additional requested regression tests -
*EXPLAIN for pattern with WHERE clause
*EXPLAIN for pattern with filters on both country and city
modified: regress/expected/index.out
modified: regress/sql/index.sql
---
regress/expected/index.out | 34 ++++++++++++++++++++++++++++++++++
regress/sql/index.sql | 14 ++++++++++++++
2 files changed, 48 insertions(+)
diff --git a/regress/expected/index.out b/regress/expected/index.out
index 9faead66..745cab26 100644
--- a/regress/expected/index.out
+++ b/regress/expected/index.out
@@ -626,6 +626,19 @@ $$) as (name agtype);
"Tijuana"
(6 rows)
+-- EXPLAIN for pattern with WHERE clause
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.country_code = 'US' AND a.west_coast = true
+ RETURN a
+$$) 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)
+ Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"country_code"'::agtype]) = '"US"'::agtype)
+(3 rows)
+
-- Test WHERE with multiple conditions (AND)
SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
@@ -700,6 +713,27 @@ $$) as (name agtype);
"Mexico"
(1 row)
+-- EXPLAIN for pattern with filters on both country and city
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (country:Country)<-[:has_city]-(city:City)
+ WHERE country.country_code = 'CA' AND city.west_coast = true
+ RETURN city.name
+$$) as (plan agtype);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Nested Loop
+ -> Index Scan using city_west_coast_idx on "City" city
+ Index Cond: (agtype_access_operator(VARIADIC ARRAY[properties,
'"west_coast"'::agtype]) = 'true'::agtype)
+ -> Bitmap Heap Scan on has_city _age_default_alias_0
+ Recheck Cond: (start_id = city.id)
+ -> Bitmap Index Scan on has_city_start_id_idx
+ Index Cond: (start_id = city.id)
+ -> Index Scan using "Country_pkey" on "Country" country
+ Index Cond: (id = _age_default_alias_0.end_id)
+ Filter: (agtype_access_operator(VARIADIC ARRAY[properties,
'"country_code"'::agtype]) = '"CA"'::agtype)
+(11 rows)
+
-- Test WHERE in combination with pattern matching
SELECT * FROM cypher('cypher_index', $$
MATCH (country:Country)<-[:has_city]-(city:City)
diff --git a/regress/sql/index.sql b/regress/sql/index.sql
index 96e7dd81..a6e075c7 100644
--- a/regress/sql/index.sql
+++ b/regress/sql/index.sql
@@ -357,6 +357,13 @@ SELECT * FROM cypher('cypher_index', $$
ORDER BY a.city_id
$$) as (name agtype);
+-- EXPLAIN for pattern with WHERE clause
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (a:City)
+ WHERE a.country_code = 'US' AND a.west_coast = true
+ RETURN a
+$$) as (plan agtype);
+
-- Test WHERE with multiple conditions (AND)
SELECT * FROM cypher('cypher_index', $$
MATCH (a:City)
@@ -404,6 +411,13 @@ SELECT * FROM cypher('cypher_index', $$
RETURN c.name
$$) as (name agtype);
+-- EXPLAIN for pattern with filters on both country and city
+SELECT * FROM cypher('cypher_index', $$
+ EXPLAIN (costs off) MATCH (country:Country)<-[:has_city]-(city:City)
+ WHERE country.country_code = 'CA' AND city.west_coast = true
+ RETURN city.name
+$$) as (plan agtype);
+
-- Test WHERE in combination with pattern matching
SELECT * FROM cypher('cypher_index', $$
MATCH (country:Country)<-[:has_city]-(city:City)
