0

Recently I have asked about Why select from function is slow?.
But now when I LEFT JOIN this function it take 11500ms.
When I rewrite LEFT JOIN by SubQuery it took only 111ms

SELECT 
(SELECT next_ots FROM order_total_suma( next_range ) next_ots 
WHERE next_ots.order_id = ots.order_id  AND next_ots.consumed_period @> (ots.o).billed_to
) AS next_suma,  --<< this took only 111ms. See plan
ots.* FROM (
    SELECT  
        tstzrange(
          NULLIF( (ots.o).billed_to, 'infinity' ),
          NULLIF( (ots.o).billed_to +p.interval, 'infinity' )
        ) as next_range,
     ots.*
    FROM order_total_suma() ots
    LEFT JOIN period p ON p.id = (ots.o).period_id
) ots
--LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = 6154 
--  AND next_ots.consumed_period @> (ots.o).billed_to --<< this is fine. plan is not posted
--LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = ots.order_id  
--  AND next_ots.consumed_period @> (ots.o).billed_to --<< this takes 11500ms. See Plan
WHERE ots.order_id IN ( 6154, 10805 )

Attached plans

While googling I have found this blog post

In most cases, joins are also a better solution than subqueries — Postgres will even internally “rewrite” a subquery, creating a join, whenever possible, but this of course increases the time it takes to come up with the query plan

Many SO question like this

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.

So why LEFT JOINing function is significantly slower in compare to SubQuery?
Is there a way to make LEFT JOIN take time equally to SubQuery?

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

0 Answers0