On Wed, Jun 1, 2016 at 5:22 AM, Amit Langote <Langote_Amit_f8@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
