Performance loss introducing lateral join in Postgres query

Question

I am dealing with a PostgreSQL (v14) query of this kind

SELECT
    ...,
    EXISTS (
        SELECT 1
        FROM table2 t2
        WHERE t2.fk = t1.id
          AND LOWER(t2.table_name) = 't1'
    ) AS t2_record_exists
FROM table1 t1;

and was hoping to extract the logic in a function to be used in lateral join, in order to compute that field in a more readable way (as it is to be used in a view).

This is the resulting query

SELECT
    ...,
    t2_record_exists.t2_record_exists
FROM table1 t1
    LEFT JOIN LATERAL does_t2_record_exist(t1.id, 't1') t2_record_exists(t2_record_exists) ON TRUE;

That makes use of this function

CREATE OR REPLACE FUNCTION does_t2_record_exist(object_id int8, _table_name text)
    RETURNS bool
    LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM table2 t2
        WHERE t2.fk = object_id
            AND LOWER(t2.table_name) = _table_name
    );
END $function$;

The second query suffers a severe performance loss, as it executes in about 6000ms, while the first gets it done in 300ms.

I don't know why this would happen, as I naively assumed that the very same operation (the EXISTS subquery) would be executed the same amount of times (once per row).

What is going wrong here? How can one foresee such performance issues beforehand?

EDIT: Here are the query plans (obtained with EXPLAIN ANALYZE) for my specific case

Query 1 (with subquery):

Index Only Scan using cos_table1 on table1 t1 (cost=0.43..3723311.61 rows=1481535 width=9) (actual time=56.299..247.674 rows=1477585 loops=1)
  Heap Fetches: 46760
  SubPlan 2
    ->  Seq Scan on table2 t2  (cost=0.00..15.27 rows=2 width=8) (actual time=11.300..11.454 rows=372 loops=1)
          Filter: (lower(table_name) = 't1'::text)
          Rows Removed by Filter: 113
Planning Time: 0.085 ms
JIT:
  Functions: 13
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.769 ms, Inlining 17.544 ms, Optimization 24.557 ms, Emission 13.652 ms, Total 56.524 ms
Execution Time: 276.285 ms

Query 2 (with LATERAL JOIN):

Nested Loop Left Join  (cost=0.68..56512.74 rows=1481535 width=9) (actual time=0.039..5978.865 rows=1477585 loops=1)
  ->  Index Only Scan using cos_table1 on table1 t1 (cost=0.43..26881.79 rows=1481535 width=8) (actual time=0.011..179.682 rows=1477585 loops=1)
        Heap Fetches: 46760
  ->  Function Scan on does_t2_record_exist t2_record_exist (cost=0.25..0.26 rows=1 width=1) (actual time=0.004..0.004 rows=1 loops=1477585)
Planning Time: 0.065 ms
Execution Time: 6024.267 ms

Consider instructions for Postgres performance questions here: stackoverflow.com/tags/postgresql-performance/info — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 28, 2024 at 13:43
If it will be wrapped into a view anyway, why does it need to be more readable? (If indeed it is, I'd argue that is less readable, though shorter). — jjanes
– jjanes, Commented Jun 28, 2024 at 15:28
@jjanes One could argue that having one line for each field gives a better looking view query where fields that are complex to compute are not too distracting. I also failed to share that that subquery should be added in multiple views, so this would also avoid repetitions. I'm sure there are good counter arguments, but the topic of the question is just understanding the performance discrepancy — Luca Blanchi
– Luca Blanchi, Commented Jun 28, 2024 at 16:10

Erwin Brandstetter · Accepted Answer · 2024-06-28 13:59:13Z

You did not show actual query plans (output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS)), but there are several performance traps in your function. Rewrite as:

CREATE OR REPLACE FUNCTION does_t2_record_exist(_object_id int8, _table_name text)
  RETURNS bool
  LANGUAGE sql STABLE PARALLEL SAFE AS  -- !
$func$
SELECT EXISTS (
   SELECT FROM table2 t2
   WHERE  t2.fk = _object_id
   AND    lower(t2.table_name) = _table_name
   );
$func$;

And your query then is simply:

SELECT ...
     , does_t2_record_exist(t1.id, 't1') AS t2_record_exists
FROM   table1 t1

No need for the LATERAL join, once you have wrapped the correlated subquery into the function.

The expression lower(t2.table_name) is not sargable. Be sure to have a (default) B-tree multicolumn, expression index on table2(fk, lower(tablename)). (Or just on one of both index fields if that is selective enough on its own.)

You don't need PL/pgSQL for the simple function. Switching to LANGUAGE sql possibly allows function inlining.

Add the function labels PARALLEL SAFE and STABLE (because they both actually apply!) to allow various performance optimizations.

Still, if anything, the function can only be slower than putting the EXISTS expression with a correlated subquery into the SELECT list directly. Very little, though, if you do everything right.

That said, a LATERAL join, or your function running a correlated subquery has to be executed once per qualifying row in the query. If that results in querying the majority of rows that a plain subquery would have to process, a join (not LATERAL) is still the fastest option. (Or your original query, which may result in the same query plan.) Like:

SELECT ...
     , t2.fk IS NOT NULL AS t2_record_exists
FROM   table1 t1
LEFT   JOIN (
   SELECT DISTINCT t2.fk  -- do we even need DISTINCT?
   FROM   table2 t2
   WHERE  lower(t2.table_name) = 't1'
   ) t2 ON t2.fk = t1.id

See:

What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

Added query plans reveal ...

Your table table2 only has 372 rows, 113 of which are filtered. That's tiny. You won't need an index! Postgres gets it done with a sequential scan in one fell swoop for the original query.
Wrapping the functionality into the black box of a PL/pgSQL function forced Postgres to execute it loops=1477585 times. That's bad.

My rewritten version may fix this. Or revert to your original query. (Or my appended alternate.)

JohnH · Accepted Answer · 2024-06-28 10:58:01Z

1

When the EXISTS operation is directly in the query, then PostgreSQL can transform the subquery into a semantically equivalent JOIN; however, when the EXISTS operation is hidden behind a function call, the query engine no longer has the information it needs to perform such optimizations. Running EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on each query should make the reason for the performance difference evident: wrapping the EXISTS in a function causes the subquery to be executed for each row instead of using a more efficient JOIN.

answered Jun 28, 2024 at 10:58

JohnH

3,2932 gold badges6 silver badges20 bronze badges

Comments

jjanes · Accepted Answer · 2024-07-01 14:45:43Z

1

This is not about the lateral join as such. The function acts as an optimization barrier. The function is being called once for each row, instead of being optimized away to be processed in bulk using hash tables or hash joins.

(lateral joins can also act as optimization barriers, but do not in this particular case)

If the view is generally used with a very restrictive WHERE clause which will be pushed into table1, this could actually turn out to be a benefit as the bulk operation can be inefficient when only a few rows will be needed.

You could use some kind of macros system to simplify repetitive code. PostgreSQL doesn't provide such, it would be up to your code repository or framework or whatever you use to manage your code.

edited Jul 1, 2024 at 14:45

answered Jun 28, 2024 at 17:40

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Collectives™ on Stack Overflow

Performance loss introducing lateral join in Postgres query

3 Answers 3

Added query plans reveal ...

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Added query plans reveal ...

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related