Re: [HACKERS] Optimizer & boolean syntax

[Daniele Orlandi]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html