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 JOIN
ing function is significantly slower in compare to SubQuery
?
Is there a way to make LEFT JOIN
take time equally to SubQuery
?