ed0sh opened a new issue, #562:
URL: https://github.com/apache/age/issues/562
**Describe the bug**
Age DBMS uses (gin) indexes only when the condition is specified within the
match clause and not in the where clause.
Moreover, the same issue seems to be present also when trying to use a btree
index on a node graphid.
**How are you accessing AGE (Command line, driver, etc.)?**
From SQL script, DBeaver CE v22.3.2.202301060755
**What data setup do we need to do?**
```pgsql
SET search_path = ag_catalog, "$user", public;
select * from create_graph('graph') ;
CREATE OR REPLACE FUNCTION public.create_random_nodes() RETURNS setof agtype
AS $$
SELECT agtype_build_map(
'Id',
substr(md5(random()::text), 1, 1000),
'Value',
(random() * 70 + 10)::integer,
'Date',
DATE '2018-01-01' + (random() * 700)::integer
)
FROM generate_series(1, 1000000);
$$ LANGUAGE sql;
select * from cypher('graph', $$
unwind [public.create_random_nodes()] as node
create (v:Visit {Id: node.Id, Value: node.Value, Date: node.Date})
$$) as (a agtype);
create index if not exists graph_visit_properties_index on graph."Visit"
using gin (properties);
create index if not exists graph_visit_properties_index on graph."Visit"
using btree (id);
```
**What is the command that caused the error?**
1.
```pgsql
select * from cypher('graph', $$
explain analyze
MATCH (v:Visit)
where v.Id = "2f10d37f58b51ffd47777bd988194be7"
RETURN v
$$) as (a agtype);
```
```
Gather (cost=1000.00..27085.75 rows=5000 width=32) (actual
time=0.289..4360.647 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on "Visit" v (cost=0.00..25585.75 rows=2083
width=32) (actual time=2849.575..4299.827 rows=0 loops=3)
Filter: (agtype_access_operator(VARIADIC
ARRAY[_agtype_build_vertex(id, _label_name('97847'::oid, id), properties),
'"Id"'::agtype]) = '"2f10d37f58b51ffd47777bd988194be7"'::agtype)
Rows Removed by Filter: 333333
Planning Time: 0.101 ms
Execution Time: 4360.860 ms
```
2.
```pgsql
select * from cypher('graph', $$
explain analyze
MATCH (v:Visit)
where id(v) = 9570149208162305
RETURN v
$$) as (a agtype);
```
```
Gather (cost=1000.00..27085.75 rows=5000 width=32) (actual
time=0.373..2663.148 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on "Visit" v (cost=0.00..25585.75 rows=2083
width=32) (actual time=1738.598..2625.160 rows=0 loops=3)
Filter: (age_id(_agtype_build_vertex(id, _label_name('97847'::oid,
id), properties)) = '9570149208162305'::agtype)
Rows Removed by Filter: 333333
Planning Time: 0.159 ms
Execution Time: 2663.197 ms
```
**Expected behavior**
I expect the behaviour of the DBMS to be the same as the following query:
```pgsql
select * from cypher('graph', $$
explain analyze
MATCH (v:Visit {Id: "2f10d37f58b51ffd47777bd988194be7"})
RETURN v
$$) as (a agtype);
```
```
Bitmap Heap Scan on "Visit" v (cost=43.75..3259.40 rows=1000 width=32)
(actual time=0.060..0.061 rows=1 loops=1)
Recheck Cond: (properties @> agtype_build_map('Id'::text,
'"2f10d37f58b51ffd47777bd988194be7"'::agtype))
Heap Blocks: exact=1
-> Bitmap Index Scan on graph_visit_properties_index (cost=0.00..43.50
rows=1000 width=0) (actual time=0.047..0.047 rows=1 loops=1)
Index Cond: (properties @> agtype_build_map('Id'::text,
'"2f10d37f58b51ffd47777bd988194be7"'::agtype))
Planning Time: 24.067 ms
Execution Time: 0.087 ms
```
**Environment:**
- Docker image: apache/age:v1.1.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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
