Kobaruon commented on issue #1974:
URL: https://github.com/apache/age/issues/1974#issuecomment-2222645906
@MironAtHome I am using postgresql 16.3
```
SELECT * from cypher('passv2', $$
MATCH (V)-[R:TCP]-(V2)
RETURN V,R,V2 LIMIT 100
$$) as (V agtype, R agtype, V2 agtype);
```
<details>
<summary> Explain analyze of the fast query </summary>
```
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1001.71..11361.42 rows=100 width=96) (actual
time=378.834..517.894 rows=100 loops=1)
-> Gather (cost=1001.71..49215657.81 rows=475058 width=96) (actual
time=378.832..517.876 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=1.71..49167152.01 rows=197941 width=96)
(actual time=232.618..504.085 rows=43 loops=3)
-> Parallel Append (cost=0.00..2285.66 rows=2488 width=179)
(actual time=0.012..0.022 rows=6 loops=3)
-> Parallel Seq Scan on mars "V2_2"
(cost=0.00..1203.92 rows=2264 width=178) (actual time=0.003..0.005 rows=5
loops=1)
-> Parallel Seq Scan on pod "V2_5" (cost=0.00..980.36
rows=745 width=264) (actual time=0.024..0.031 rows=7 loops=1)
-> Parallel Seq Scan on dne "V2_6" (cost=0.00..45.92
rows=464 width=53) (never executed)
-> Parallel Seq Scan on venus "V2_3"
(cost=0.00..11.58 rows=19 width=164) (never executed)
-> Parallel Seq Scan on earth "V2_4" (cost=0.00..3.49
rows=16 width=159) (actual time=0.002..0.003 rows=2 loops=1)
-> Parallel Seq Scan on smars "V2_7" (cost=0.00..2.04
rows=1 width=52) (actual time=0.001..0.003 rows=2 loops=1)
-> Parallel Seq Scan on _ag_label_vertex "V2_1"
(cost=0.00..1.02 rows=1 width=35) (actual time=0.007..0.008 rows=1 loops=1)
-> Parallel Seq Scan on abc "V2_8" (cost=0.00..0.00
rows=1 width=51) (actual time=0.001..0.001 rows=0 loops=1)
-> Nested Loop (cost=1.71..19580.50 rows=5970 width=244)
(actual time=52.545..88.865 rows=8 loops=17)
-> Append (cost=0.00..2411.62 rows=5970 width=179)
(actual time=0.002..3.528 rows=5287 loops=17)
-> Seq Scan on _ag_label_vertex "V_1"
(cost=0.00..1.03 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=17)
-> Seq Scan on mars "V_2" (cost=0.00..1251.47
rows=3849 width=178) (actual time=0.004..1.836 rows=3498 loops=17)
-> Seq Scan on venus "V_3" (cost=0.00..11.99
rows=33 width=164) (actual time=0.008..0.022 rows=33 loops=15)
-> Seq Scan on earth "V_4" (cost=0.00..3.84
rows=28 width=159) (actual time=0.004..0.010 rows=28 loops=15)
-> Seq Scan on pod "V_5" (cost=0.00..996.01
rows=1267 width=264) (actual time=0.005..1.007 rows=1227 loops=15)
-> Seq Scan on dne "V_6" (cost=0.00..55.67
rows=789 width=53) (actual time=0.006..0.139 rows=789 loops=14)
-> Seq Scan on smars "V_7" (cost=0.00..2.06
rows=2 width=52) (actual time=0.016..0.018 rows=2 loops=14)
-> Seq Scan on abc "V_8" (cost=0.00..0.00
rows=1 width=51) (actual time=0.002..0.002 rows=0 loops=14)
-> Bitmap Heap Scan on "TCP" "R" (cost=1.71..2.85
rows=1 width=65) (actual time=0.014..0.014 rows=0 loops=89876)
Recheck Cond: (((end_id = "V2".id) AND (start_id
= "V".id)) OR ((end_id = "V".id) AND (start_id = "V2".id)))
Heap Blocks: exact=7
-> BitmapOr (cost=1.71..1.71 rows=1 width=0)
(actual time=0.014..0.014 rows=0 loops=89876)
-> Bitmap Index Scan on idx_tcp_end_start
(cost=0.00..0.85 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=89876)
Index Cond: ((end_id = "V2".id) AND
(start_id = "V".id))
-> Bitmap Index Scan on idx_tcp_end_start
(cost=0.00..0.85 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=89876)
Index Cond: ((end_id = "V".id) AND
(start_id = "V2".id))
Planning Time: 3.619 ms
Execution Time: 518.101 ms
(34 rows)
```
</details>
I tried your recommendation and it seems to make the query faster, however
the query itself is already faster without making any changes for some reason.
It's still slower than what we expected, but it's a great improvement.
Original: Takes 19.8sec
https://explain.dalibo.com/plan/dhe464cb1579b734
Tweaked: 2.7sec
BEGIN;
SET LOCAL parallel_leader_participation = off;
explain analyze SELECT * from cypher('passv2', $$
MATCH (V)-[R:WORKED_ON]-(V2)
RETURN V,R,V2
LIMIT 100
$$) as (V agtype, R agtype, V2 agtype);
https://explain.dalibo.com/plan/5f29h6473626777e
--
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]
