It is with shame that I admit this problem was nobody's fault by mine.
After running some queries with "EXPLAIN ANALYZE", and asking for help from the
postgres-NOVICE mailing list, it was pointed out that the server didn't have
good estimates about the header values table (it was only off by about 1
million rows) and consequently it was making poor decisions on executing the
query. You may further read about my mistakes on that list.
Anyway -- long story short, rewriting that code may have been completely
unnecessary, and I'm very sorry for pointing Paul in the wrong direction on
this. That code may save a few miliseconds on an optimized table, but it will
truly come in handy to someone that didn't add a "-z" to their vacuum cron job.
Maybe for future ref, it would be wise to first ask anyone on postgres
complaining about slow queries if they have VACUUM'd with the ANALYZE flag, and
then of course beat them with a small wooden cane.
Thank you Paul and I am sincerely sorry for wasting valuable programming time.
Brian Neu <[EMAIL PROTECTED]> wrote: 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
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
