Conditional selection in FROM clause

Question

I have two tables:

parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.

There are additional columns, but I did not list them here as they are not really relevant.

I want to select the periods as following:

If the parent matches some predicate, then pick parent.period.
If not, then pick child.period from each child.

I have the following query:

select   q.period
from     parent,
lateral  (
           select  parent.period
           where   <some complex predicate>
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(<the same complex predicate as above>)
         ) as q(period);

While this query works and returns the expected periods, I am wondering if this query could be rewritten in such way that <some complex predicate> does not have to be evaluated twice, to improve performance (albeit by a small factor) and general query structure to represent the if ... else ... from above. Ideally, I want to loop once through each parent/child row and evaluate the predicate just once.

I tried to introduce a case when <some complex predicate> then ... else ... end clause, but I fail to get it right. I am not sure if that is even possible in the FROM clause. Is this possible?

I also had select case when <predicate> then parent.period else unnest(array(select child.period from child where ...)) from parent in mind (though I did not try it, so it might be disallowed in a case when ... as well), but I am not sure if first creating and then unnesting an array is really optimal. Also, I am not really fond of set-returning functions, such as unnest, in the selection.

score 1 · Accepted Answer · answered Oct 15 '19 at 09:10

The following queries evaluate the expensive predicate just once for each parent row. To achieve this, the predicate is evaluated in a separate subquery. The first version uses a lateral join, the second an inner join. This meets the requirements from the question.

select   q2.period
from     parent,
lateral  (
           select <some complex predicate>
         ) as q1(predicate),
lateral  (
           select  parent.period
           where   q1.predicate
           union all
           select  child.period
           from    child
           where   child.parent_identifier = parent.identifier and
                   not(q1.predicate)
         ) as q2(period);

select     q2.period
from       parent,
inner join (
             select  parent.identifier, <some complex predicate>
             from    parent
           ) as q1(identifier, predicate)
on         parent.identifier = q1.identifier,
lateral    (
             select  parent.period
             where   q1.predicate
             union all
             select  child.period
             from    child
             where   child.parent_identifier = parent.identifier and
                     not(q1.predicate)
           ) as q2(period);

Conditional selection in FROM clause

1 Answers1