Re: [HACKERS] Change in order of criteria - reg
On Wed, Jun 1, 2016 at 12:07 AM, 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 ?? > > Why are you asking? Do you have any context in which you want to measure "sameness"? I was thinking that pg_stat_statements might treat them differently but the comparison there is object based, not string based, so these should end up with the same hash. If everything is working correctly there will be no observable and persistent difference between executing those exact two queries as far as PostgreSQL is concerned. You might find a difference in the parse tree representation where a > 5 is on the left leaf of a branch in one query but on the right leaf in the other... David J.
Re: [HACKERS] Change in order of criteria - reg
On Wed, Jun 1, 2016 at 5:22 AM, Amit Langote 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
