Index is not used in the WHERE clause

[vladiksun]

Describe the bug

Index is not used in the WHERE clause.

How are you accessing AGE (Command line, driver, etc.)?

• JDBC

What data setup do we need to do?

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?

select
    any_profile
from ag_catalog.cypher('test_graph',$$
    EXPLAIN ANALYZE MATCH (any_profile:`profile` { hidden: false })
    WHERE any_profile.pet = 'dog'
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);

QUERY PLAN
Bitmap Heap Scan on profile any_profile (cost=20.08..52.40 rows=1 width=32) (actual time=1.111..22.752 rows=1616 loops=1)
Recheck Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('17463'::oid, id), properties), '"pet"'::agtype]) = '"dog"'::agtype)
Rows Removed by Filter: 3384
Heap Blocks: exact=143
-> Bitmap Index Scan on profile_gin_idx (cost=0.00..20.08 rows=10 width=0) (actual time=1.010..1.010 rows=5000 loops=1)
Index Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
Planning Time: 0.733 ms
Execution Time: 22.869 ms

Expected behavior
Either of two indexes should be used

Environment (please complete the following information):

• Version: [e.g. 1.3.0]

Additional context

As we see from the plan the filter function is applied as:
Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('17463'::oid, id), properties), '"pet"'::agtype]) = '"dog"'::agtype)

We also tried to simulate the functional index like this with no luck because there is a mutable function involved: \

CREATE INDEX profile_pet_btree_idx3 ON test_graph."profile" USING BTREE (
ag_catalog.agtype_access_operator(VARIADIC ARRAY[age_properties(_agtype_build_vertex(id, _label_name('16945'::oid, id), properties)), '"pet"'::ag_catalog.agtype])
);

Could it be possible to add an index support for the WHERE clause at least via any functional index by not involving mutable functions ?

[jrgemignani]

There is a patch in the works that "may" address this. But, others are welcome to look into it.

[vladiksun]

@jrgemignani Is there a branch for the patch you mentioned ?

[jrgemignani]

@vladiksun The patch I'm referring to isn't in a branch atm. Currently, it is under review for completeness.

[jrgemignani]

@MuhammadTahaNaveed @Zainab-Saad Could you look into whether the patch (Josh's) you are reviewing/reworking might address this or not?

[MuhammadTahaNaveed]

@MuhammadTahaNaveed @Zainab-Saad Could you look into whether the patch (Josh's) you are reviewing/reworking might address this or not?

We checked and it doesnt seem that patch solves this issue.

[jrgemignani]

@vladiksun We are still looking into this, it's been a busy past month.

[jrgemignani]

edited: removed. I realized it wasn't what I thought it was. The index, apparently isn't used until there is a threshold reached.

[jrgemignani]

@vladiksun I was able to get an index working for WHERE -

psql-15.3-5432-pgsql=# select
    any_profile
from ag_catalog.cypher('test_graph',$$
    EXPLAIN ANALYZE MATCH (any_profile:`profile` { hidden: false })
    WHERE any_profile.pet = 'dog'
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);
                                                                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on profile any_profile  (cost=53.67..57.70 rows=1 width=32) (actual time=69.444..425.885 rows=33347 loops=1)
   Recheck Cond: ((agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties), '"pet"
'::agtype]) = '"dog"'::agtype) AND (properties @> agtype_build_map('hidden'::text, 'false'::agtype)))
   Heap Blocks: exact=2858
   ->  BitmapAnd  (cost=53.67..53.67 rows=1 width=0) (actual time=68.563..68.564 rows=0 loops=1)
         ->  Bitmap Index Scan on profile_pet_btree_idx5  (cost=0.00..23.92 rows=1000 width=0) (actual time=38.343..38.343 rows=6666
6 loops=1)
               Index Cond: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties
), '"pet"'::agtype]) = '"dog"'::agtype)
         ->  Bitmap Index Scan on profile_gin_idx  (cost=0.00..29.50 rows=200 width=0) (actual time=29.980..29.980 rows=99961 loops=
1)
               Index Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
 Planning Time: 1.457 ms
 Execution Time: 430.522 ms
(10 rows)

I still need to do other tests to verify that the changes don't cause any side effects. There are 2 issues here -

• Are the functions truly IMMUTABLE? This needs to be verified.
• The profile_pet_btree_idx3 was not correct. But, without being able to create it, you wouldn't know.

[vladiksun]

@jrgemignani thank you for taking a look. Indeed without creating a functional index there is no way to check if that actuality might work.
If you were able to create such an index, one thing seems suspicious to me. With an index the actual execution time is longer than without it. But this probably depends on the setup.
For basic queries like searching the vertex by its property we decided not to use Cypher calls, but instead usual SQL queries with functional indexes around properties values. It is much faster and we can use native FTS this way. I believe this is possible because agtype is a superset of a jsonb hence we can use it like jsonb.
But for traversal queries the index should help reduce the filtering time of incoming or outgoing vertices.
For example queries with scenarios - find all outgoing vertices with label 'A' from the start vertex label 'B' where some 'A''s property IN [value1, value2]. I assume with more hops involved ('A's could be chained in a hierarchy) such queries take longer without index on a specific property.

[jrgemignani]

@vladiksun There are 2 things that will cause a difference in execution time. The first is that I modified your for loop to 100000. The second is that I ran it locally on a small server of mine. Tomorrow I will try to give you before and after times with your original query.

[jrgemignani]

@vladiksun Here are the results using your original query.

Before adding 3rd index

psql-15.3-5432-pgsql=# select
    any_profile
from ag_catalog.cypher('test_graph',$$
    EXPLAIN ANALYZE MATCH (any_profile:`profile` { hidden: false })
    WHERE any_profile.pet = 'dog'
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);
                                                                         QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
------------------------
 Bitmap Heap Scan on profile any_profile  (cost=20.08..52.40 rows=1 width=32) (actual time=1.763..47.451 rows=1721 loops=1)
   Recheck Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
   Filter: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties), '"pet"'::agty
pe]) = '"dog"'::agtype)
   Rows Removed by Filter: 3308
   Heap Blocks: exact=143
   ->  Bitmap Index Scan on profile_gin_idx  (cost=0.00..20.08 rows=10 width=0) (actual time=1.653..1.653 rows=5029 loops=1)
         Index Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
 Planning Time: 2.109 ms
 Execution Time: 47.805 ms
(9 rows)

Adding 3rd index

psql-15.3-5432-pgsql=# CREATE INDEX profile_pet_btree_idx3 ON test_graph."profile" USING BTREE (
agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties), '"pet"'::agtype]));
CREATE INDEX

After adding 3rd index

psql-15.3-5432-pgsql=# select
    any_profile
from ag_catalog.cypher('test_graph',$$
    EXPLAIN ANALYZE MATCH (any_profile:`profile` { hidden: false })
    WHERE any_profile.pet = 'dog'
    RETURN any_profile
$$
) as (any_profile ag_catalog.agtype);
                                                                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on profile any_profile  (cost=24.99..29.02 rows=1 width=32) (actual time=5.618..24.466 rows=1721 loops=1)
   Recheck Cond: ((agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties), '"pet"
'::agtype]) = '"dog"'::agtype) AND (properties @> agtype_build_map('hidden'::text, 'false'::agtype)))
   Heap Blocks: exact=143
   ->  BitmapAnd  (cost=24.99..24.99 rows=1 width=0) (actual time=5.534..5.534 rows=0 loops=1)
         ->  Bitmap Index Scan on profile_pet_btree_idx3  (cost=0.00..4.66 rows=50 width=0) (actual time=4.081..4.081 rows=3416 loop
s=1)
               Index Cond: (agtype_access_operator(VARIADIC ARRAY[_agtype_build_vertex(id, _label_name('16954'::oid, id), properties
), '"pet"'::agtype]) = '"dog"'::agtype)
         ->  Bitmap Index Scan on profile_gin_idx  (cost=0.00..20.08 rows=10 width=0) (actual time=1.439..1.439 rows=5029 loops=1)
               Index Cond: (properties @> agtype_build_map('hidden'::text, 'false'::agtype))
 Planning Time: 1.436 ms
 Execution Time: 24.784 ms
(10 rows)

psql-15.3-5432-pgsql=#