4

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.

  • Do you need more than one row or one column from child, per row of parent? – jjanes Oct 11 at 18:39
  • @jjanes A parent can have multiple children, so I want to have the period columns of all those children, as demonstrated in the query. I do not need to have other child properties, besides the period column. Do you have something like SELECT CASE WHEN <predicate> THEN parent.period ELSE unnest(array(SELECT child.period FROM child WHERE ...)) FROM parent in mind? – pbillen Oct 11 at 18:48
  • that is more or less what I had in mind, although I think the unnest would have to go into an outer select wrapped around the one with the array_agg. (Or return the array_agg to the client and have the client deal with it). I can't think of an option that will meet your preferences better than you already have. – jjanes Oct 11 at 19:26
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.