Paul,
After seeing your patch, I had a bad feeling that maybe the problem wasn't
isolated to when the hi and lo values were the same. So this morning, I did
some testing of ranges the way they are written in this query with different
values. Unfortunately, the problem remains, even with different values. I
don't know how Postgres resolves the query, because the testing I do doesn't
seem viable in the application. However, if this info helps, know that
changing the "BETWEEN" clause to an "IN" gives 70ms queries as well.
AND message_idnr IN
(253770,253771,253772,253773,253774,253775,253776,253777,253778,253779)
I've reviewed the index structure, reading through the Postgres docs . . . this
is out of my league.
Maybe it's something worth asking the postgres list about to get an
explanation?
Thanks again for addressing this. It is a huge, huge help to me.
UPDATE(I hadn't hit send): I haven't yet come up with a query that works. The
SVN pegged postgres even worse than 2.2.5 so I'm still solutionless.
Paul J Stevens <[EMAIL PROTECTED]> wrote: Paul J Stevens wrote:
> Brian Neu wrote:
>> It's the "BETWEEN" construct, or what it does rather.
>>
>> "AND message_idnr >= 253775 AND message_idnr <= 253775" did the same thing.
>>
>> "message_idnr = 253775" brought a 70ms query, instead of 7+ seconds.
>>
>> Sooo . . . . . . . ummmm. What would people that are smarter than me in
>> this arena suggest that I do about this? Obviously someone chose a range
>> for a reason.
>
> I'll fix this before 2.2.6
Brian,
I've fixed this in dbmail_2_2_branch.
I'm attaching the patch so you can apply it yourself.
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
>From 05756a49505611fdcbc9a8b127aef6baae4bcaf6 Mon Sep 17 00:00:00 2001
From: Paul J Stevens
Date: Thu, 21 Jun 2007 10:14:20 +0200
Subject: collapse BETWEEN clause if hi and low values are equal
---
dbmail-imapsession.c | 37 +++++++++++++++++++++++++++++--------
1 files changed, 29 insertions(+), 8 deletions(-)
diff --git a/dbmail-imapsession.c b/dbmail-imapsession.c
index b72273c..578dc72 100644
--- a/dbmail-imapsession.c
+++ b/dbmail-imapsession.c
@@ -32,6 +32,7 @@
#define SEND_BUF_SIZE 1024
#define MAX_ARGS 512
#define IDLE_TIMEOUT 30
+#define RANGE_SIZE 128
extern db_param_t _db_params;
#define DBPFX _db_params.pfx
@@ -700,6 +701,7 @@ int dbmail_imap_session_fetch_parse_args(struct ImapSession
* self)
return 1; //theres more...
}
+
GTree * dbmail_imap_session_get_msginfo(struct ImapSession *self, GTree *ids)
{
@@ -711,8 +713,9 @@ GTree * dbmail_imap_session_get_msginfo(struct ImapSession
*self, GTree *ids)
GList *l, *t;
u64_t *uid, *lo, *hi;
u64_t id;
- char query[DEF_QUERYSIZE];
+ char query[DEF_QUERYSIZE], range[RANGE_SIZE];
memset(query,0,DEF_QUERYSIZE);
+ memset(range,0,RANGE_SIZE);
if (! (ids && g_tree_nnodes(ids)>0))
return NULL;
@@ -734,15 +737,20 @@ GTree * dbmail_imap_session_get_msginfo(struct
ImapSession *self, GTree *ids)
db_free_result();
+ if (*lo == *hi)
+ snprintf(range,RANGE_SIZE,"= %llu", *lo);
+ else
+ snprintf(range,RANGE_SIZE,"BETWEEN %llu AND %llu", *lo, *hi);
+
snprintf(query, DEF_QUERYSIZE,
"SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, "
"draft_flag, recent_flag, %s, rfcsize, message_idnr "
"FROM %smessages msg, %sphysmessage pm "
"WHERE pm.id = msg.physmessage_id "
- "AND message_idnr BETWEEN %llu AND %llu "
+ "AND message_idnr %s "
"AND mailbox_idnr = %llu AND status IN (%d,%d,%d) "
"ORDER BY message_idnr ASC",to_char_str,DBPFX,DBPFX,
- *lo, *hi, ud->mailbox.uid,
+ range, ud->mailbox.uid,
MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN,MESSAGE_STATUS_DELETE);
g_free(to_char_str);
@@ -1043,7 +1051,9 @@ static void _fetch_envelopes(struct ImapSession *self)
u64_t id;
static int lo = 0;
static u64_t hi = 0;
+ char range[RANGE_SIZE];
GList *last;
+ memset(range,0,RANGE_SIZE);
if (! self->envelopes) {
self->envelopes =
g_tree_new_full((GCompareDataFunc)ucmp,NULL,(GDestroyNotify)g_free,(GDestroyNotify)g_free);
@@ -1062,13 +1072,18 @@ static void _fetch_envelopes(struct ImapSession *self)
last = g_list_last(self->ids_list);
hi = *(u64_t *)last->data;
+ if (self->msg_idnr == hi)
+ snprintf(range,RANGE_SIZE,"= %llu", self->msg_idnr);
+ else
+ snprintf(range,RANGE_SIZE,"BETWEEN %llu AND %llu", self->msg_idnr, hi);
+
g_string_printf(q,"SELECT message_idnr,envelope "
"FROM %senvelope e "
"JOIN %smessages m ON m.physmessage_id=e.physmessage_id "
"WHERE m.mailbox_idnr = %llu "
- "AND message_idnr BETWEEN %llu AND %llu ",
+ "AND message_idnr %s",
DBPFX, DBPFX,
- self->mailbox->id, self->msg_idnr, hi);
+ self->mailbox->id, range);
if (db_query(q->str)==-1)
return;
@@ -1151,6 +1166,8 @@ static void _fetch_headers(struct ImapSession *self,
body_fetch_t *bodyfetch, gb
static int lo = 0;
static u64_t hi = 0;
static u64_t ceiling = 0;
+ char range[RANGE_SIZE];
+ memset(range,0,RANGE_SIZE);
if (! self->headers) {
TRACE(TRACE_DEBUG, "init self->headers");
@@ -1196,16 +1213,20 @@ static void _fetch_headers(struct ImapSession *self,
body_fetch_t *bodyfetch, gb
last = g_list_last(self->ids_list);
hi = *(u64_t *)last->data;
+ if (self->msg_idnr == hi)
+ snprintf(range,RANGE_SIZE,"= %llu", self->msg_idnr);
+ else
+ snprintf(range,RANGE_SIZE,"BETWEEN %llu AND %llu", self->msg_idnr, hi);
+
g_string_printf(q,"SELECT message_idnr,headername,headervalue "
"FROM %sheadervalue v "
"JOIN %smessages m ON v.physmessage_id=m.physmessage_id "
"JOIN %sheadername n ON v.headername_id=n.id "
"WHERE m.mailbox_idnr = %llu "
- "AND message_idnr BETWEEN %llu AND %llu "
+ "AND message_idnr %s "
"AND lower(headername) %s IN ('%s')",
DBPFX, DBPFX, DBPFX,
- self->mailbox->id,
- self->msg_idnr, hi,
+ self->mailbox->id, range,
not?"NOT":"", bodyfetch->hdrnames);
if (db_query(q->str)==-1)
--
1.5.1
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
