Dynamically filtering a CTE?

["W. Trevor King" <wking@xxxxxxxxxx>]

I have a slow ‘WITH RECURSIVE’ CTE like:

  CREATE VIEW ancestors AS
  WITH RECURSIVE _ancestors(descendant, ancestors) AS (
      SELECT
        item.id AS id,
        ARRAY[item.ancestor_id] AS ancestors
      FROM items AS item
    UNION ALL
      SELECT
        child.id AS id
        child.ancestors || ancestor.ancestor_id AS ancestors
      FROM _ancestors AS child
      JOIN items as ancestor
        ON child.ancestors[array_length(child.ancestors, 1)] = ancestor.id
  )
  SELECT *
  FROM _ancestors
  WHERE child.ancestors[array_length(child.ancestors, 1)] IS NULL;

I'll usually only need a few rows, so I'm being bitten by PostgreSQL's
CTE optimization fence [1].  I'm looking to optimize it by limiting
the dynamically limiting the number of rows in the initial query, as
if it had been:

  WITH RECURSIVE _ancestors(id, ancestors) AS (
      SELECT
        item.id AS id,
        ARRAY[item.ancestor_id] AS ancestors
      FROM items AS item
      WHERE {your condition here}
    UNION ALL
      …
  )

My initial thought was to create a function which accepted a WITH
clause as an argument.  Something like:

  CREATE OR REPLACE FUNCTION ancestors(condition)
    RETURNS TABLE(id integer, ancestors integer[]) AS
  $$
    WITH RECURSIVE _ancestors(id, ancestors) AS (
        SELECT
          item.id AS id,
          ARRAY[item.ancestor_id] AS ancestors
        FROM items AS item
        WHERE condition
      UNION ALL
        …
    )
    …
  $$ LANGUAGE SQL;

or with ‘WHERE condition(item)’.  But I couldn't find a way to define
an argument that was a where condition [2] or a record→boolean
function [3].  I could probably use PREPARE/EXECUTE [4] to dynamically
construct the WHERE statement, but that looks like it may have its own
optimization issues and there's no way to stash it for use in
subsequent sessions.  Perhaps a function to run the PREPARE?  Is there
an idiomatic way to approach this problem?

Thanks,
Trevor

[1]: https://www.postgresql.org/message-id/201209191305.44674.db@xxxxxxxxx
[2]: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-WHERE
[3]: https://www.postgresql.org/docs/10/static/sql-createfunction.html
[4]: https://www.postgresql.org/docs/10/static/sql-prepare.html

-- 
This email may be signed or encrypted with GnuPG (http://www.gnupg.org).
For more information, see http://en.wikipedia.org/wiki/Pretty_Good_Privacy

Attachment: signature.asc
Description: OpenPGP digital signature