On Wed, Jun 1, 2016 at 5:22 AM, Amit Langote
<langote_amit...@lab.ntt.co.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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to