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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
