jrgemignani commented on code in PR #2300:
URL: https://github.com/apache/age/pull/2300#discussion_r2677566441
##########
regress/expected/index.out:
##########
@@ -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', $$
Review Comment:
> Explain for pattern with where should be added as well imo.
Explain with WHERE is done above, many times, already.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
