[GitHub] [age] rafsun42 commented on issue #1000: Index is not used in the WHERE clause

["rafsun42 (via GitHub)" <gi...@apache.org>]

rafsun42 commented on issue #1000:
URL: https://github.com/apache/age/issues/1000#issuecomment-1710885960

   @vladiksun I tried recreating the 'IN' query using the 'ANY' operator, but in SQL (instead of Cypher).
   ```sql
   EXPLAIN ANALYZE
   SELECT * FROM test_graph.profile
   WHERE
   	agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('49745'::oid, id), properties), '"pet"'::agtype])
               = ANY(ARRAY['"dog"'::agtype, '"cat"'::agtype])
   	AND properties @> agtype_build_map('hidden'::text, 'false'::agtype);
   ```
   
   I get this query plan:
   ```
   Bitmap Heap Scan on profile  (cost=9.32..148.87 rows=1 width=40) (actual time=13.369..59.118 rows=3326 loops=1)
      Recheck Cond: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('49745'::oid, id), properties), '"pet"'::agtype]) = ANY ('{"\"dog\"","\"cat\""}'::agtype[]))
      Filter: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
      Rows Removed by Filter: 3296
      Heap Blocks: exact=143
      ->  Bitmap Index Scan on profile_pet_btree_idx31  (cost=0.00..9.32 rows=100 width=0) (actual time=13.189..13.191 rows=6622 loops=1)
            Index Cond: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('49745'::oid, id), properties), '"pet"'::agtype]) = ANY ('{"\"dog\"","\"cat\""}'::agtype[]))
    Planning Time: 0.667 ms
    Execution Time: 59.693 ms
   (9 rows)
   ```
   
   Let me know if this SQL query is what you are looking for and if it uses index on your machine. We may have a patch that transforms IN into an ANY. We can work on integrating that into master.
   


-- 
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-unsubscribe@age.apache.org

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