Kobaruon opened a new issue, #1974:
URL: https://github.com/apache/age/issues/1974
This simple query takes about 3 minutes to run. Is this normal behaviour? Is
there a room for improvement here in terms of indexing or rewriting the query?
```
SELECT * from cypher('passv2', $$
MATCH (V)-[R:WORKED_ON]-(V2)
RETURN V,R,V2
LIMIT 100
$$) as (V agtype, R agtype, V2 agtype);
```
```
Table "passv2.WORKED_ON"
Column | Type | Collation | Nullable |
Default
------------+--------------------+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------
id | ag_catalog.graphid | | not null |
ag_catalog._graphid(ag_catalog._label_id('passv2'::name,
'WORKED_ON'::name)::integer, nextval('"WORKED_ON_id_seq"'::regclass))
start_id | ag_catalog.graphid | | not null |
end_id | ag_catalog.graphid | | not null |
properties | ag_catalog.agtype | | not null |
ag_catalog.agtype_build_map()
Indexes:
"WORKED_ON_end_id_start_id_idx" btree (end_id, start_id)
"WORKED_ON_id_idx" btree (id)
"WORKED_ON_properties_idx" gin (properties)
"WORKED_ON_start_id_end_id_idx" btree (start_id, end_id)
"idx_worked_on_end_id" btree (end_id)
"idx_worked_on_start_id" btree (start_id)
Inherits: _ag_label_edge
```
<details>
<summary> Explain Analyze </summary>
```
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1000.57..30222613.91 rows=100 width=96) (actual
time=180465.331..182428.507 rows=100 loops=1)
Buffers: shared hit=202943877
-> Gather (cost=1000.57..414641535.58 rows=1372 width=96) (actual
time=180465.329..182428.494 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=202943877
-> Nested Loop (cost=0.57..414640398.38 rows=572 width=96)
(actual time=61699.042..92998.472 rows=53 loops=3)
Buffers: shared hit=202943877
-> Nested Loop (cost=0.00..24546776.28 rows=224054050
width=472) (actual time=0.022..9394.517 rows=16767051 loops=3)
Buffers: shared hit=1738966
-> Parallel Append (cost=0.00..1355.14 rows=9663
width=236) (actual time=0.008..0.928 rows=724 loops=3)
Buffers: shared hit=49
-> Parallel Seq Scan on pod "V_6"
(cost=0.00..997.72 rows=10791 width=257) (actual time=0.005..0.501 rows=681
loops=1)
Buffers: shared hit=26
-> Parallel Seq Scan on mars "V_3"
(cost=0.00..165.77 rows=1992 width=199) (actual time=0.007..0.020 rows=18
loops=2)
Buffers: shared hit=2
-> Parallel Seq Scan on "DNE" "V_2"
(cost=0.00..40.30 rows=810 width=52) (actual time=0.004..1.122 rows=1377
loops=1)
Buffers: shared hit=16
-> Parallel Seq Scan on earth "V_5"
(cost=0.00..2.78 rows=26 width=162) (actual time=0.002..0.034 rows=44 loops=1)
Buffers: shared hit=2
-> Parallel Seq Scan on venus "V_4"
(cost=0.00..1.55 rows=18 width=220) (actual time=0.003..0.026 rows=31 loops=1)
Buffers: shared hit=1
-> Parallel Seq Scan on "euCentral01" "V_7"
(cost=0.00..1.07 rows=2 width=59) (actual time=0.001..0.003 rows=4 loops=1)
Buffers: shared hit=1
-> Parallel Seq Scan on _ag_label_vertex "V_1"
(cost=0.00..1.02 rows=1 width=35) (actual time=0.003..0.004 rows=1 loops=1)
Buffers: shared hit=1
-> Parallel Seq Scan on host "V_8"
(cost=0.00..0.00 rows=1 width=79) (actual time=0.001..0.001 rows=0 loops=1)
-> Append (cost=0.00..1844.47 rows=23189 width=236)
(actual time=0.002..7.452 rows=23148 loops=2173)
Buffers: shared hit=1738917
-> Seq Scan on _ag_label_vertex "V2_1"
(cost=0.00..1.03 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=2173)
Buffers: shared hit=2173
-> Seq Scan on "DNE" "V2_2" (cost=0.00..57.31
rows=1377 width=52) (actual time=0.002..0.191 rows=1377 loops=2173)
Buffers: shared hit=34768
-> Seq Scan on mars "V2_3" (cost=0.00..207.61
rows=3387 width=199) (actual time=0.003..0.576 rows=3386 loops=2173)
Buffers: shared hit=230245
-> Seq Scan on venus "V2_4" (cost=0.00..1.93
rows=31 width=220) (actual time=0.004..0.007 rows=31 loops=2172)
Buffers: shared hit=2172
-> Seq Scan on earth "V2_5" (cost=0.00..3.32
rows=44 width=162) (actual time=0.002..0.008 rows=44 loops=2172)
Buffers: shared hit=4344
-> Seq Scan on pod "V2_6" (cost=0.00..1224.32
rows=18344 width=257) (actual time=0.001..3.281 rows=18314 loops=2172)
Buffers: shared hit=1463045
-> Seq Scan on "euCentral01" "V2_7"
(cost=0.00..1.12 rows=4 width=59) (actual time=0.002..0.002 rows=4 loops=2170)
Buffers: shared hit=2170
-> Seq Scan on host "V2_8" (cost=0.00..0.00
rows=1 width=79) (actual time=0.000..0.000 rows=0 loops=2170)
-> Bitmap Heap Scan on "WORKED_ON" "R" (cost=0.57..1.71
rows=1 width=51) (actual time=0.003..0.003 rows=0 loops=50301154)
Recheck Cond: (((start_id = "V".id) AND (end_id =
"V2".id)) OR ((start_id = "V2".id) AND (end_id = "V".id)))
Heap Blocks: exact=1
Buffers: shared hit=201204731
-> BitmapOr (cost=0.57..0.57 rows=1 width=0) (actual
time=0.003..0.003 rows=0 loops=50301154)
Buffers: shared hit=201204624
-> Bitmap Index Scan on
"WORKED_ON_start_id_end_id_idx" (cost=0.00..0.29 rows=1 width=0) (actual
time=0.001..0.001 rows=0 loops=50301154)
Index Cond: ((start_id = "V".id) AND
(end_id = "V2".id))
Buffers: shared hit=100602316
-> Bitmap Index Scan on
"WORKED_ON_start_id_end_id_idx" (cost=0.00..0.29 rows=1 width=0) (actual
time=0.001..0.001 rows=0 loops=50301154)
Index Cond: ((start_id = "V2".id) AND
(end_id = "V".id))
Buffers: shared hit=100602308
Planning:
Buffers: shared hit=6
Planning Time: 0.694 ms
Execution Time: 182428.614 ms
(60 rows)
```
</details>
--
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]
