sql - postgres: does where clause filters applied in order they return

Question

Below is query has 3 filters in where condition, which filter is applied first on table ? does filters applied on table in the order right to left written in query.

I want product_key to be filtered out first and then remaining filters.

select *
from order_history
where product_key in (select product_key from products
                      where status = 'avilable' )
  and province = 'Texas'
  and category_key in (3,4,5)

question from:https://stackoverflow.com/questions/66047310/postgres-does-where-clause-filters-applied-in-order-they-return

Answer 1

which filter is applied first on table ?

As stated in a comment, the optimizer determines the order. It should do so in an informed way -- I would assume the constant comparisons would be before the in. And some of them might be converted to indexes.

You could insist on your own ordering using a case expression:

select oh.*
from order_history oh
where (case when province is distinct from 'Texas' then false
            when category_key not in (3, 4, 5) then false
            else product_key in (select product_key
                                 from products
                                 where status = 'available'
                                )
       end);

I strongly, strongly advise you never to use this approach, because it prevents the optimizer from choosing better execution plans -- for instance, the query won't use indexes or partitions.

However, the case expression does evaluate the conditions in order, so you could insist on a particular ordering with such a construct.