This select is almost instant:
WITH RECURSIVE pathname(id, parent_id, basename) AS (
SELECT child.id, child.parent_id, child.basename
FROM dirents child
WHERE basename = '10732.emlx'
UNION ALL
SELECT parent.id, parent.parent_id, CONCAT(parent.basename, '/', child.basename)
FROM dirents parent, pathname child
WHERE parent.id = child.parent_id
)
SELECT basename FROM pathname where parent_id IS NULL;
Note that the non-recursive term selects the children and the recursion is “out” towards the ancestors.
This select doesn’t complete before I get impatient:
CREATE RECURSIVE VIEW pathname(id, basename, parent_id, ino, ext, fullpath) AS
SELECT id, basename, parent_id, ino, ext, basename
FROM dirents
WHERE parent_id IS NULL
UNION ALL
SELECT child.id, child.basename, child.parent_id, child.ino, child.ext, CONCAT(parent.fullpath, '/', child.basename)
FROM dirents child, pathname parent
WHERE parent.id = child.parent_id;
SELECT * FROM pathname WHERE basename = '10732.emlx’;
In this case, the non-recursive term starts at the top of the directory trees and the recursion works “in” towards the children.
I’m not surprised that the first is fast and the second is very slow. My problem is I currently have a file called recurse.sql which is the top query. I go in and edit that file and then execute it via psql -f recurse.sql. What I’m attempting to do in the second example is to create a view and then use select on the view to select the rows that I’m looking for.
To rephrase, is it possible to write a view that would work from the child terms out towards the ancestors?
Thank you for your time,
Perry