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

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

On Wed, Jun 1, 2016 at 12:07 AM, sri harsha <sriharsha9992@gmail.com> 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.