Thread: Order of execution

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 

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