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
