I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6.
I have the following tables in my database: folders, users, groups, permissions.
# \d users; Column | Type | Nullable id | int | not null name | text | not null # \d groups; Column | Type | Nullable id | int | not null name | text | not null # \d folders; Column | Type | Nullable id | int | not null parent_id | int | name | text | not null # \d permissions; Column | Type | Nullable id | int | not null folder_id | int | not null user_id | int | group_id | int | type | text | -- constraint: user_id IS NULL <> group_id IS NULL -- type: e.g. 'read' or 'edit'
I also have a view called folder_ancestry that runs a recursive CTE query on the folders table to build an ancestry tree. It's very fast:
# SELECT * FROM folder_ancestry; folder_id | ancestor_id | distance 1 | NULL | 1 2 | NULL | 2 2 | 1 | 1
Well, this works
I am trying to get the closest user (
user_id) or group (
group_id) permission for each
folder_id. I have achieved this with a rank() windowed function, which is pretty fast:
WITH permission_ancestry AS ( SELECT permissions.user_id, permissions.group_id, permissions.id AS permission_id, permissions.type AS permission_type, permissions.folder_id AS permission_folder_id, folder_ancestry.folder_id, CASE WHEN folder_ancestry.ancestor_id IS NULL THEN 0 ELSE folder_ancestry.distance END AS distance, RANK() OVER ( PARTITION BY permissions.user_id, permissions.group_id, folder_ancestry.folder_id ORDER BY ( CASE WHEN folder_ancestry.ancestor_id IS NULL THEN 0 ELSE folder_ancestry.distance END ) ) AS rank FROM permissions INNER JOIN folder_ancestry ON COALESCE(folder_ancestry.ancestor_id, folder_ancestry.folder_id) = permissions.folder_id ) SELECT * FROM permission_ancestry WHERE permission_ancestry.rank = 1
Here is where I'm facing a performance problem. If I query the above with a specific
folder_id, the query is super fast as it only looks up permissions for a specific
folder_id. However, when I join the query above with another table, the planner will gather every folder permission, and then filter it down to the 3 that I actually need:
SELECT * FROM file_versions INNER JOIN files ON files.id = file_versions.file_id INNER JOIN folder_permissions ON folder_permissions.folder_id = files.folder_id WHERE file_versions.status = 'complete'
Is there a way I can change something so that the planner finds the file folders first, and only then runs the query on those
Or is there a better way to write the permission summarizer query that gets unique relationships without having to run another query to check where the
rank = 1?
Thank you for your help.
I have changed the folder_ancestry view to have 0 distances so that I don't have to use a case statement when trying to find the closest permission.
I also created a function called ancestry() that does the same things as the folder_ancestry view, but for a single folder ID.
The literal join with a function in the fiddle is very slow for some reason; however, I'm getting similar results when using literal join with a function vs using literal join with a view on live data.
I suppose I could move everything (including the rank() window function) into a new permissions function, but I was hoping I could get away with views.
Observation on live data
What's interesting, is that I was tinkering with the queries on the live data, and noticed that Postgres pushes down predicates nicely into the "with" part of the query that uses the folder_ancestry view with a lateral join. It does not do the push down the same predicates as soon as I move the "with" part into its own view.
I tried researching this and found that rCTE and aggregation might break pushing things down, but even after trying to make folder_ancestry a materialized view and changing window aggregator to a distinct on(), the planner still fails to push predicates.
I ended up removing the RANK() window function in favor of DISTINCT ON and it has sped up the query about performance 10x.