With Recursive / Recursive View question

[Perry Smith <pedz@xxxxxxxxxxxxxxxx>]

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

Attachment: signature.asc
Description: Message signed with OpenPGP