On Mon, Oct 23, 2006 at 10:01:04AM -0700, Aaron Stone wrote:
> On Mon, 2006-10-23 at 11:13 -0500, Jim C. Nasby wrote:
> > On Fri, Oct 20, 2006 at 12:05:43AM -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;
> >
> > Your results are 100% meaningless, because they do not show how long it
> > actually took to run the queries. Without the data from EXPLAIN ANALYZE
> > there is no basis for changing the query.
>
> Do you have such data available?
>
> > BTW, if you are going to change it, it would be more clear to do BETWEEN
> > 0 AND 1, which the optimizer should be able to deal with just as well as
> > < 2.
>
> I am perfectly comfortable making whatever change works best. Do you
> have data that shows how the BETWEEN is optimized?
No, I don't actually use dbmail... I'm here because I'm a bit of a
PostgreSQL zealot and want to offer support to projects that are using
it. The OP or someone else will need to post the output of EXPLAIN
ANALYZE from the different versions of that query. I suspect that it
will still show that either BETWEEN or > 2 are the best bet, but if
you're going to change code you should have actual data to back that
change up, not just what the planner is guessing (which is all that a
plain EXPLAIN shows). Also, this will depend on how large the indexes
are, so it would be good if people with different size databases can run
the test (I believe that the larger the database, the less it will
matter which version is used).
BTW, I also noticed one email showed "IN ('1', '2')" - there's no reason
to quote parameters because that makes the planner treat them as text.
On some versions, that means it won't use the index at all, on some
others it means it has to re-cast the text, which is just wasted cycles.
--
Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
