The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX revision_idx_id ON "revision" ("id");
CREATE INDEX revision_idx_has_snapshot ON "revision" ("has_snapshot");

The optimized query:

WITH RECURSIVE rev_list (id, parent_revision_id, depth) AS (SELECT
        t.id,
        t.parent_revision_id,
        1 
    FROM
        revision t 
    WHERE
        t.id = $1 
    UNION
    ALL SELECT
        t.id,
        t.parent_revision_id,
        r.depth + 1 
    FROM
        rev_list r 
    JOIN
        revision t 
            ON t.id = r.parent_revision_id 
    WHERE
        t.has_snapshot = false) SELECT
        t.id,
        t.parent_revision_id,
        t.depth 
    FROM
        rev_list t 
    ORDER BY
        t.id