Re: Optimizer & boolean syntax

Stephan Szabo wrote:
> On Thu, 21 Nov 2002, Daniele Orlandi wrote:
> 
> 
>>Are those two syntaxes eqivalent ?
>>
>>select * from users where monitored;
>>select * from users where monitored=true;
>>
>>If the answer is yes, the optimimer probably doesn't agree with you :)
> 
> 
> That depends on the definition of equivalent.

By equivalent I mean "means the same thing so, behaves in the same way".

I consider the former syntax to be cleaner and I would tend to use it 
most of times.

For what concerns partial indexes, I agree, it's a better approach for 
this kind of indexing and I did some test:

-------------------------
ctonet=# create index users_monitored on users (monitored) where monitored;
CREATE
ctonet=# explain select * from users where monitored;
NOTICE:  QUERY PLAN:

Index Scan using users_monitored on users  (cost=0.00..9.44 rows=6 
width=186)

EXPLAIN

Nice, it appears to use the index, but:


ctonet=# explain select * from users where monitored=true;
NOTICE:  QUERY PLAN:

Seq Scan on users  (cost=0.00..8298.84 rows=59 width=186)

EXPLAIN
-------------------------

The problem is the opposite... so, effectively, seems that the optimizer 
considers "monitored" and "monitored=true" as two different expressions...

The viceversa is analog and we also can see that the syntax "monitored 
is true" is considered different from the other two syntaxes:

-----------------------
ctonet=# drop index users_monitored;
DROP
ctonet=# create index users_monitored on users (monitored) where 
monitored=true;
CREATE
ctonet=# explain select * from users where monitored=true;
NOTICE:  QUERY PLAN:

Index Scan using users_monitored on users  (cost=0.00..9.45 rows=6 
width=186)

EXPLAIN
ctonet=# explain select * from users where monitored;
NOTICE:  QUERY PLAN:

Seq Scan on users  (cost=0.00..8077.07 rows=59 width=186)

EXPLAIN

ctonet=# create index users_monitored on users (monitored) where 
monitored=true;
CREATE
ctonet=# explain select * from users where monitored is true;
NOTICE:  QUERY PLAN:

Seq Scan on users  (cost=0.00..8077.07 rows=59 width=186)

EXPLAIN
-------------------------

What I propose is that all those syntaxes are made equivalent (by, for 
example, rewriting boolean comparisons to a common form) in order to 
have a more consistent index usage.

Bye!

--  Daniele Orlandi Planet Srl