Re: [HACKERS] Change in order of criteria - reg

On 2016/06/01 13:07, sri harsha wrote:
> Hi,
>
> In PostgreSQL , does the order in which the criteria is given matter ??
> For example
>
> Query 1 : Select * from TABLE where a > 5 and b < 10;
>
> Query 2 : Select * from TABLE where b <10 and a > 5;
>
> Are query 1 and query 2 the same in PostgreSQL or different ?? If its
> different , WHY ??

tl;dr they are the same. As in they obviously produce the same result and
result in invoking the same plan.

Internally, optimizer will order application of those quals in resulting
plan based on per-tuple cost of individual quals. So a cheaper, more
selective qual might result in short-circuiting of relatively expensive
quals for a large number of rows in the table saving some cost in
run-time. Also, if index scan is chosen and quals pushed down, the
underlying index method might know to order quals smartly.

However, the cost-markings of operators/functions involved in quals better
match reality. By default, most operators/functions in a database are
marked with cost of 1 unit. Stable sorting used in ordering of quals
would mean the order of applying quals in resulting plan matches the
original order (ie, the order in which they appear in the query). So, if
the first specified qual really happens to be an expensive qual but marked
as having the same cost as other less expensive quals, one would have to
pay the price of evaluating it for all the rows. Whereas, correctly
marking the costs could have avoided that (as explained above). Note that
I am not suggesting that ordering quals in query by their perceived cost
is the solution. Keep optimizer informed by setting costs appropriately
and it will do the right thing more often than not. :)

Thanks,
Amit