If we can change it, then I agree it should be changed. The database
can't optimized IN(0,1) to < 2 because it doesn't know if negative
values should be included, we don't have them but the optimizer doesn't
really know that.
Aaron Stone wrote:
Ok, I think we should change the query in db_getmailbox_count to be < 2
because the plan really is a lot less expensive than IN (0, 1). I know
this was a thread/bug we had a week or two ago, but it popped into my
head today that maybe the planner was guessing the IN (0, 1) meant the
same at < 2. It doesn't, and even on Pg 8, it's still more expensive.
Aaron
dbmail=# explain select message_idnr from dbmail_messages where
mailbox_idnr = 1 and status in (0, 1) order by message_idnr asc;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------
Sort (cost=9.67..9.67 rows=1 width=8)
Sort Key: message_idnr
-> Index Scan using dbmail_messages_8, dbmail_messages_8 on
dbmail_messages (cost=0.00..9.66 rows=1 width=8)
Index Cond: (((mailbox_idnr = 1) AND (status = 0)) OR
((mailbox_idnr = 1) AND (status = 1)))
Filter: ((mailbox_idnr = 1) AND ((status = 0) OR (status = 1)))
(5 rows)
dbmail=# explain select message_idnr from dbmail_messages where
mailbox_idnr = 1 and status < 2 order by message_idnr asc;
QUERY
PLAN
-----------------------------------------------------------------------------------------------
Sort (cost=8.31..8.31 rows=2 width=8)
Sort Key: message_idnr
-> Index Scan using dbmail_messages_8 on dbmail_messages
(cost=0.00..8.30 rows=2 width=8)
Index Cond: ((mailbox_idnr = 1) AND (status < 2))
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
