Postgres LATERAL Query Correctness and Efficiency

Question

I have the following structure of data, with table names give in bold font and their pertinent column names below.

common_authorprofile:

{id, full_name, description, avatar_id, profile_id}

aldryn_people_person table:

{id, phone, ...}

aldryn_newsblog_article:

{id, is_published, is_featured, ..., author_id}

It bears noting that common_authorprofile.profile_id = aldryn_people_person.id and aldryn_newsblog_article.author_id = aldryn_people_person.id

I am trying to compute the number of articles for each entity in common_authorprofile.

This is how it is currently done:

SELECT main.*, sub.article_count
FROM common_authorprofile AS main
INNER JOIN aldryn_people_person
ON aldryn_people_person.id = main.profile_id,
LATERAL 
    (SELECT author_id, COUNT(*) as article_count
     FROM aldryn_newsblog_article AS sub
     WHERE
            sub.author_id = aldryn_people_person.id   AND
            sub.app_config_id = 1            AND
            sub.is_published IS TRUE         AND
            sub.publishing_date <= now()     AND
        aldryn_people_person.id = sub.author_id 
     GROUP BY author_id
    ) AS sub

My question is two-fold:

is this a correct way of doing it, given the table relationship?
is this an efficient way, i.e., is there a way to improve its speed and readability?

Are the relationships correct as you described them? If so, then why are you bringing `aldryn_people_person` into the mix since `common_authorprofile.profile_id = aldryn_newsblog_article.author_id`? — Mike Organek, Jul 29 '20 at 17:45
@MikeOrganek, I believe your observation is correct. This query is a result of a bit of trial-and-error. There are probably redundant bits in it. — MadPhysicist, Jul 29 '20 at 17:51

score 1 · Answer 1 · answered Jul 29 '20 at 17:59

Dropping aldryn_people_person out of the mix makes this easier to read.

I also prefer common table expressions over subqueries or lateral joins for readability, but CTEs can slow down execution. I refactor only if speed is a problem.

I would approach it like this:

with article_counts as (
  select author_id, count(*) as article_count
    from aldryn_newsblog_article
   where app_config_id = 1
     and is_published
     and publishing_date <= now()
   group by author_id
)
select prof.*, coalesce(ac.article_count, 0) as article_count
  from common_authorprofile prof
       left join article_counts ac
              on ac.author_id = prof.profile_id;

The left outer join buys you the retrieval of all common_authorprofile records. The coalesce() displays missing rows from the article_counts CTE as 0. You can change the left join to just join if that is not what you want.

If you have any questions, please comment.

Thank you. That is an interesting approach. A lot of new stuff there for me. Suppose you wanted to restrict `common_authorprofile.id` in consideration to a set that is provided dynamically. Would you simply add a `WHERE common_authorprofile.id IN ...` clause at the very bottom? As a concrete example, it might be `WHERE common_authorprofile.id IN (1,2)`. — MadPhysicist, Jul 29 '20 at 18:24
@MadPhysicist Yes, except it would be `prof.id` the way I aliased the table. — Mike Organek, Jul 29 '20 at 18:25

Postgres LATERAL Query Correctness and Efficiency

1 Answers1