Well now I'm just hugely confused, because the cost appears to be lower when using < 2. Of course I don't care which we use, I just want to be using whichever clause is definitively cheaper!
Aaron On Fri, 2006-10-20 at 12:16 -0600, Jesse Norell wrote: > Hello, > > I'm not saying this change shouldn't be made, but make sure you keep > in mind the original reason/issue that changed from "status < 2" to > "status in (0,1)" in the first place: > > http://mailman.fastxs.net/pipermail/dbmail-dev/2005-October/007594.html > and > http://www.dbmail.org/mantis/view.php?id=278 > > We saw a huge performance improvement ourselves, using pop3. That's was > on postgres 7.4, and maybe/probably things have improved with later > versions. Also note in the single function that was causing us > problems, you could completely eliminate the status comparison with > basically no adverse effect (so maybe eliminate it there, and change it > to a < 2 comparison everywhere else if that needs done?). > > Jesse > > > > On Fri, 2006-10-20 at 00:05 -0700, 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
