How to tune age-viewer to load graph faster

[dianaoa22]

I have a AGE DB populated with the below vertex and edges, however age viewer takes several hours to load this. How to tune viewer to load this faster.

nspdb=# SELECT * FROM cypher('test_graph', $$ MATCH (n) RETURN COUNT(n) $$) AS (vertex_count agtype); vertex_count

227867 (1 row) nspdb=# SELECT * FROM cypher('test_graph', $$ MATCH ()-[r]->() RETURN COUNT(r) $$) AS (edge_count agtype); edge_count

227902 (1 row)

[MironAtHome]

Could you please use Explain Analyze and upload plans here: https://explain.dalibo.com/ or just paste here so that we could inspect in detail, where Postgres spends time, when performing queries. Also, please see if this hint

BEGIN;
SET LOCAL parallel_leader_participation = off;
explain analyze SELECT * from cypher(
...

learned earlier on some parallel query ( I would imagine postgres tries to invoke this query using parallel threads ) makes positive impact on query duration. Ideally it would also be useful to do something to reduce I/O, like passing an id column to count function, something like

count(v.id)

assuming id is indexed field in the vertex, however, it's not a given, that scanning index would outrun scanning table, normally database query optimizer should be able to make good decision regarding organization of physical activities, however, in case above it seems that postgres for some reason performs steps less than optimal way, so, it would be really nice to experiment a bit. In the end counting even 1 million rows normally should take minutes.

In addition, please do make sure to check, in order of appearance, closer to the top, higher priority 1 parallel activity on the same server 2 check database for anything like physical file corruption 3 run vacuum 4 please check logs for any exception or indicators of unusual activities

--- specific note --- It is unlikely that the cause of this specific issue is in some deeply ornate workings of optimizer, but since we are here, it makes sense to see if performing count in a loop against individual vertices can help. Here is a script that counts rows for just vertices:

load 'age';
set search_path = ag_catalog, "$user", public;
do
$RUN$
declare
	count_tally_var_temp bigint;
    count_tally_var      bigint = 0;
    sql_statement_var    varchar(1000000);
    vertice_name_var     varchar(256);
begin

    load 'age';
    set search_path = ag_catalog, "$user", public;

    for vertice_name_var
        in
            SELECT trim(cast(ag_catalog.agtype_out(vertice_name) as varchar(256)), '"')
            FROM cypher('test_graph', $$
                MATCH(v)
                    WITH label(v) as vertice_name
                        RETURN DISTINCT vertice_name
            $$) AS t(vertice_name agtype)
            ORDER BY 1 ASC
        loop

            sql_statement_var := CONCAT('SELECT * FROM cypher(''test_graph'',$$ MATCH (v:'
                                      , vertice_name_var
                                      , ') return ag_catalog.agtype_to_int8(count(v)) $$) AS t(count_rows BIGINT)');

            EXECUTE sql_statement_var
                INTO count_tally_var_temp;

            RAISE NOTICE 'vertice: %, row count: %, time: %'
                           , vertice_name_var
                           , count_tally_var_temp
                           , current_time;

            count_tally_var := coalesce(count_tally_var, 0) + coalesce(count_tally_var_temp, 0);

        end loop;
			
        RAISE NOTICE 'count_tally_var = %', count_tally_var;

end;
$RUN$;

At least this should help to note which vertice performs slower to narrow down performance tuning effort.

Going back to troubleshooting steps: Top 1 is something to be expected and checked first. Since resources on RDBMS are always shared and if a lot of things happening at the same time, it is expected that overall operations run slower. The further to 4, the more it's a system level check for troubleshooting, catching something out of order.

Although it's not something I would suggest, and I haven't tried it myself, yet, if any of these queries carry significant importance, please see what is the frequency and expected freshness of data. If similar query is critical to your production system please consider materializing data to table every so often in procedure. Querying table itself should be very quick, and in fact in this case it could be useful to have the looping construct, to tally per vertex / edge and than sum totals in the query.

Please share plans if above hints do not net good improvement for the next step in tuning. Hope it helps.

Corrected code producing counts.

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove "Abondoned" label or comment or this will be closed in 14 days.

[github-actions[bot]]

This issue was closed because it has been stalled for further 14 days with no activity.