jrgemignani commented on issue #1000:
URL: https://github.com/apache/age/issues/1000#issuecomment-1673440840
@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=#
```
--
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
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
