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

On Wed, Jun 1, 2016 at 5:22 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> 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. :)

I think that if the costs are actually identical, the system will keep
the quals in the same order they were written - so then the order does
matter, a little bit.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company