Thread: Order of execution

Order of execution

From
luis.roberto@siscobra.com.br
Date:
Hi!

My question is: is it possible to optimize function order execution?

Here's explanation:

I have a bunch of queries that have volatile quals, some more than one. For example:

SELECT *
  FROM clients
 WHERE some_func(client_id)
   AND some_other_func(client_id) 

Now, I know that having volatile function quals is not a good practice, but alas, it is what it is.

In this contrived example, some_func filters about 50% of clients, whereas some_other_func around 5%.

If PostgreSQL would execute them "serially", some_other_func would only run for 50% of the clients, cutting execution
time.What I've seen is that volatile functions execute always.
 

Is the reason this happen because the function can modify the result from the outer query?

Luis R. Weck 



Re: Order of execution

From
Jean-Christophe Boggio
Date:
Hello,

Le 27/04/2021 à 20:52, luis.roberto@siscobra.com.br a écrit :
> My question is: is it possible to optimize function order execution?

I guess you could change the cost of one of the functions.


I personally rewrite my queries but I don't know if it's good practice:

WITH pre AS (
   SELECT client_id
   FROM clients
   WHERE some_other_func(client_id)
)
SELECT *
FROM clients
JOIN pre USING(client_id)
WHERE some_func(client_id)

Best regards,

JC