[GitHub] [age] vladiksun opened a new issue, #1235: Index is not used in the WHERE clause for IN operator

[via GitHub]

vladiksun opened a new issue, #1235:
URL: https://github.com/apache/age/issues/1235

   **Describe the bug**
   Index is not used in the WHERE clause for IN operator
   
   **Additional context**
   @rafsun42 proposed the possible solution in the thread 
https://github.com/apache/age/issues/1000#issuecomment-1710885960.
   As agreed with @jrgemignani in 
https://github.com/apache/age/issues/1000#issuecomment-1723626748 this issue 
deserves a separate ticket.
   
   
   **How are you accessing AGE (Command line, driver, etc.)?**
   - JDBC
   
   **What data setup do we need to do?**
   ```pgsql
   LOAD 'age';
   SET search_path = ag_catalog, "$user", public;
   
   select ag_catalog.create_graph('test_graph');
   select ag_catalog.create_vlabel('test_graph','profile');
   
   -- works only for MATCH because GIN only makes sense for pattern matching 
what MATCH clause is
   CREATE INDEX profile_gin_idx ON test_graph."profile" USING GIN (properties);
   
   -- does not work in the where clause
   CREATE INDEX profile_pet_btree_idx1 ON test_graph."profile" USING BTREE 
((properties -> 'pet'));
   
   -- does not work in the where clause
   CREATE INDEX profile_pet_btree_idx2 ON test_graph."profile" USING BTREE 
(ag_catalog.agtype_access_operator(properties, '"pet"'::ag_catalog.agtype));
   
   ------ generate more data -------
   DO
   $do$
       BEGIN
           FOR i IN 1..10000 LOOP
               EXECUTE format('
                   select * from ag_catalog.cypher(''test_graph'',
                   $$
                       CREATE (any_vertex: profile { `id`: "%s", `pet`: "%s", 
`hidden`: %s })
                       RETURN any_vertex
                   $$
               ) as (any_vertex ag_catalog.agtype)',
                      (SELECT uuid_in(md5(random()::text || 
now()::text)::cstring)),
                      (SELECT 
('[0:2]={dog,cat,bird}'::text[])[floor(random()*3)]),
                      (SELECT ('[0:1]={true,false}'::text[])[floor(random()*2)])
                   );
           END LOOP;
       END
   $do$;
   
   ```
   **What is the command that caused the error?**
   ```pgsql
   select
       any_profile
   from ag_catalog.cypher('test_graph',$$
       EXPLAIN ANALYZE
       MATCH (any_profile:`profile` { hidden: false })
       WHERE any_profile.pet IN ['dog', 'cat']
       RETURN any_profile
   $$
   ) as (any_profile ag_catalog.agtype);
   ```
   
   **Environment (please complete the following information):**
   - Version: 1.4.0
   
   
   


-- 
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: dev-unsubscr...@age.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org