jrgemignani commented on issue #2194:
URL: https://github.com/apache/age/issues/2194#issuecomment-3190070294
@serdarmicrosoft I removed the rest leaving -
```
psql-17.5-5432-pgsql=# select tablename,indexname,indexdef from pg_indexes
where schemaname = 'goodreads_graph_x';
tablename | indexname |
indexdef
------------------+-----------------------+--------------------------------------------------------------------------------------------------
_ag_label_vertex | _ag_label_vertex_pkey | CREATE UNIQUE INDEX
_ag_label_vertex_pkey ON goodreads_graph_x._ag_label_vertex USING btree (id)
_ag_label_edge | _ag_label_edge_pkey | CREATE UNIQUE INDEX
_ag_label_edge_pkey ON goodreads_graph_x._ag_label_edge USING btree (id)
(2 rows)
```
**SQL**
```
psql-17.5-5432-pgsql=# explain analyze select count(*), u.id from
goodreads_graph_x."User" u, goodreads_graph_x."HAS_INTERACTION" h,
goodreads_graph_x."Book" b where u.id = h.start_id and b.id = h.end_id GROUP BY
u.id ORDER BY count(*) DESC LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------
Limit (cost=838170.89..838170.92 rows=10 width=16) (actual
time=4981.262..5005.469 rows=10 loops=1)
-> Sort (cost=838170.89..839782.61 rows=644686 width=16) (actual
time=4981.260..5005.466 rows=10 loops=1)
Sort Key: (count(*)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=660908.50..824239.46 rows=644686
width=16) (actual time=4854.597..4977.144 rows=132661 loops=1)
Group Key: u.id
-> Gather Merge (cost=660908.50..811345.74 rows=1289372
width=16) (actual time=4854.587..4916.484 rows=135688 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=659908.48..661520.19 rows=644686
width=16) (actual time=4848.318..4855.496 rows=45229 loops=3)
Sort Key: u.id
Sort Method: quicksort Memory: 3022kB
Worker 0: Sort Method: quicksort Memory: 2916kB
Worker 1: Sort Method: quicksort Memory: 2912kB
-> Partial HashAggregate
(cost=547684.39..586683.96 rows=644686 width=16) (actual
time=4812.863..4827.311 rows=4522
9 loops=3)
Group Key: u.id
Planned Partitions: 8 Batches: 1 Memory
Usage: 6929kB
Worker 0: Batches: 1 Memory Usage: 6673kB
Worker 1: Batches: 1 Memory Usage: 6673kB
-> Parallel Hash Join
(cost=22063.08..313304.87 rows=4166747 width=8) (actual time=2693.933..3832.376
rows=33
33333 loops=3)
Hash Cond: (h.start_id = u.id)
-> Parallel Hash Join
(cost=3853.15..228504.20 rows=4166747 width=8) (actual time=18.426..1859.249
rows
=3333333 loops=3)
Hash Cond: (h.end_id = b.id)
-> Parallel Seq Scan on
"HAS_INTERACTION" h (cost=0.00..184515.47 rows=4166747 width=16) (actual
time=0.034..500.278 rows=3333333 loops=3)
-> Parallel Hash
(cost=3166.40..3166.40 rows=54940 width=8) (actual time=17.692..17.693 rows=3113
3 loops=3)
Buckets: 131072 Batches:
1 Memory Usage: 4736kB
-> Parallel Seq Scan on
"Book" b (cost=0.00..3166.40 rows=54940 width=8) (actual time=0.009
..8.255 rows=31133 loops=3)
-> Parallel Hash
(cost=13802.19..13802.19 rows=268619 width=8) (actual time=118.693..118.694
rows=21489
5 loops=3)
Buckets: 262144 (originally
262144) Batches: 16 (originally 8) Memory Usage: 12384kB
-> Parallel Seq Scan on "User"
u (cost=0.00..13802.19 rows=268619 width=8) (actual time=0.049..55
.689 rows=214895 loops=3)
Planning Time: 0.411 ms
Execution Time: 5007.980 ms
(32 rows)
psql-17.5-5432-pgsql=#
```
**u_idc**
```
psql-17.5-5432-pgsql=# SELECT * FROM cypher('goodreads_graph_x', $$ explain
analyze MATCH (u:User)-[:HAS_INTERACTION]->(:Book) RETURN count(*), u_idc ORDER
BY count(*) DESC LIMIT 10 $$) AS (count agtype, id agtype);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
Subquery Scan on _ (cost=839782.61..839782.66 rows=10 width=64) (actual
time=5105.792..5129.789 rows=10 loops=1)
-> Limit (cost=839782.61..839782.63 rows=10 width=40) (actual
time=5105.788..5129.780 rows=10 loops=1)
-> Sort (cost=839782.61..841394.32 rows=644686 width=40) (actual
time=5105.786..5129.777 rows=10 loops=1)
Sort Key: ((count(*))::agtype) DESC
Sort Method: top-N heapsort Memory: 26kB
-> Finalize GroupAggregate (cost=660908.50..825851.17
rows=644686 width=40) (actual time=4855.134..5041.501 rows=132661 loops=1
)
Group Key: u.id
-> Gather Merge (cost=660908.50..811345.74
rows=1289372 width=16) (actual time=4855.120..4919.445 rows=134560 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=659908.48..661520.19 rows=644686
width=16) (actual time=4848.028..4856.092 rows=44853 loops=3)
Sort Key: u.id
Sort Method: quicksort Memory: 2922kB
Worker 0: Sort Method: quicksort Memory:
2907kB
Worker 1: Sort Method: quicksort Memory:
2986kB
-> Partial HashAggregate
(cost=547684.39..586683.96 rows=644686 width=16) (actual
time=4813.103..4827.149 row
s=44853 loops=3)
Group Key: u.id
Planned Partitions: 8 Batches: 1
Memory Usage: 6673kB
Worker 0: Batches: 1 Memory Usage:
6673kB
Worker 1: Batches: 1 Memory Usage:
6929kB
-> Parallel Hash Join
(cost=22063.08..313304.87 rows=4166747 width=8) (actual time=2698.029..3826.705
r
ows=3333333 loops=3)
Hash Cond:
(_age_default_alias_0.start_id = u.id)
-> Parallel Hash Join
(cost=3853.15..228504.20 rows=4166747 width=8) (actual time=18.387..1856.38
1 rows=3333333 loops=3)
Hash Cond:
(_age_default_alias_0.end_id = _age_default_alias_1.id)
-> Parallel Seq Scan on
"HAS_INTERACTION" _age_default_alias_0 (cost=0.00..184515.47 rows=4
166747 width=16) (actual time=0.034..501.547 rows=3333333 loops=3)
-> Parallel Hash
(cost=3166.40..3166.40 rows=54940 width=8) (actual time=17.673..17.674 row
s=31133 loops=3)
Buckets: 131072
Batches: 1 Memory Usage: 4704kB
-> Parallel Seq
Scan on "Book" _age_default_alias_1 (cost=0.00..3166.40 rows=54940 wi
dth=8) (actual time=0.018..8.248 rows=31133 loops=3)
-> Parallel Hash
(cost=13802.19..13802.19 rows=268619 width=8) (actual time=127.643..127.644 rows
=214895 loops=3)
Buckets: 262144
(originally 262144) Batches: 16 (originally 8) Memory Usage: 12352kB
-> Parallel Seq Scan on
"User" u (cost=0.00..13802.19 rows=268619 width=8) (actual time=0.0
42..56.620 rows=214895 loops=3)
Planning Time: 0.481 ms
Execution Time: 5132.179 ms
(33 rows)
psql-17.5-5432-pgsql=#
```
**id(u)**
```psql-17.5-5432-pgsql=# SELECT * FROM cypher('goodreads_graph_x', $$
explain analyze MATCH (u:User)-[:HAS_INTERACTION]->(:Book) RETURN count(*),
id(u) ORDER BY count(*) DESC LIMIT 10 $$) AS (count agtype, id agtype);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=1016207.67..1016207.69 rows=10 width=64) (actual
time=17369.620..17386.003 rows=10 loops=1)
-> Sort (cost=1016207.67..1017819.38 rows=644686 width=64) (actual
time=17369.618..17386.000 rows=10 loops=1)
Sort Key: ((count(*))::agtype) DESC
Sort Method: top-N heapsort Memory: 26kB
-> Finalize GroupAggregate (cost=832498.42..1002276.23
rows=644686 width=64) (actual time=16953.437..17306.058 rows=132661 loops=1)
Group Key: (age_id(_agtype_build_vertex(u.id,
_label_name('237668'::oid, u.id), u.properties)))
-> Gather Merge (cost=832498.42..982935.65 rows=1289372
width=40) (actual time=16953.419..17099.214 rows=132995 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=831498.39..833110.11 rows=644686
width=40) (actual time=16943.086..16950.235 rows=44332 loops=3)
Sort Key: (age_id(_agtype_build_vertex(u.id,
_label_name('237668'::oid, u.id), u.properties)))
Sort Method: quicksort Memory: 3384kB
Worker 0: Sort Method: quicksort Memory: 3440kB
Worker 1: Sort Method: quicksort Memory: 3507kB
-> Partial HashAggregate
(cost=683413.13..751662.38 rows=644686 width=40) (actual
time=16656.683..16671.162 rows=44
332 loops=3)
Group Key:
age_id(_agtype_build_vertex(u.id, _label_name('237668'::oid, u.id),
u.properties))
Planned Partitions: 16 Batches: 1 Memory
Usage: 6417kB
Worker 0: Batches: 1 Memory Usage: 6417kB
Worker 1: Batches: 1 Memory Usage: 6673kB
-> Parallel Hash Join
(cost=25473.08..351375.47 rows=4166747 width=32) (actual
time=2687.049..13682.043 rows=
3333333 loops=3)
Hash Cond:
(_age_default_alias_0.start_id = u.id)
-> Parallel Hash Join
(cost=3853.15..228504.20 rows=4166747 width=8) (actual time=18.775..1868.630
rows
=3333333 loops=3)
Hash Cond:
(_age_default_alias_0.end_id = _age_default_alias_1.id)
-> Parallel Seq Scan on
"HAS_INTERACTION" _age_default_alias_0 (cost=0.00..184515.47 rows=4166747
width=16) (actual time=0.032..504.195 rows=3333333 loops=3)
-> Parallel Hash
(cost=3166.40..3166.40 rows=54940 width=8) (actual time=18.347..18.348 rows=3113
3 loops=3)
Buckets: 131072 Batches:
1 Memory Usage: 4704kB
-> Parallel Seq Scan on
"Book" _age_default_alias_1 (cost=0.00..3166.40 rows=54940 width=8)
(actual time=0.010..8.536 rows=31133 loops=3)
-> Parallel Hash
(cost=13802.19..13802.19 rows=268619 width=110) (actual time=111.960..111.961
rows=214
895 loops=3)
Buckets: 65536 (originally
65536) Batches: 32 (originally 16) Memory Usage: 9824kB
-> Parallel Seq Scan on "User"
u (cost=0.00..13802.19 rows=268619 width=110) (actual time=0.045..
38.799 rows=214895 loops=3)
Planning Time: 0.448 ms
Execution Time: 17388.427 ms
(32 rows)
psql-17.5-5432-pgsql=#
```
Not much difference. This could be because the data was loaded in an orderly
way making indexes unnecessary?
--
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]
