The following issue has been RESOLVED.
======================================================================
http://www.dbmail.org/mantis/view.php?id=278
======================================================================
Reported By: davel
Assigned To: paul
======================================================================
Project: DBMail
Issue ID: 278
Category: Database layer
Reproducibility: always
Severity: feature
Priority: normal
Status: resolved
Resolution: fixed
Fixed in Version:
======================================================================
Date Submitted: 25-Oct-05 18:44 CEST
Last Modified: 30-Apr-06 00:49 CEST
======================================================================
Summary: query optimization
Description:
Function "db_update_pop" has query (postgres version):
"UPDATE messages set status=%llu::bigint
WHERE message_idnr=%llu::bigint AND status<%d::smallint"
I recommend that the status comparator be dropped from the
query in all versions of the code.
This results in a gain in performance (in pgsql at least),
and only adds "questionable" behaviour which would only
be noticed by multiple pop clients accessing the same
mailbox at the same time.
Also, the initial cast to ::bigint for the status should
probably be changed to ::smallint to reflect schema.
My initial email with more technical detail is attached.
======================================================================
----------------------------------------------------------------------
paul - 26-Oct-05 09:00
----------------------------------------------------------------------
Jesse,
I suggest a slightly different approach: instead of using
status<MESSAGE_STATUS_DELETE
I suspect that
status IN (MESSAGE_STATUS_NEW,MESSAGE_STATUS_SEEN)
will give us the same behaviour as is currently the case, but will also
perform much better on postgres.
Could you provide some benchmark here?
----------------------------------------------------------------------
davel - 26-Oct-05 16:28
----------------------------------------------------------------------
paul, it's dave, not jesse :)
i know, normally i just sit idly by and let him do all the work....
using that modified query results in the same sub-second time,
and uses a different index:
explain UPDATE messages set status=2::smallint WHERE
message_idnr=48698661::bigint AND status in (0,1);
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using messages_pkey on messages (cost=0.00..5.88 rows=1
width=94)
Index Cond: (message_idnr = 48698661::bigint)
Filter: ((status = 0) OR (status = 1))
i find this an acceptable solution, even more so that it doesn't change
behaviour. however, i'm still curious as to why the behaviour (logic)
is this way in the first place?
----------------------------------------------------------------------
paul - 26-Oct-05 21:58
----------------------------------------------------------------------
Hi Dave,
This all happened way before my time on dbmail, but this does strike me as
good logic trying to avoid race conditions. The popd should not be allowed
to set status to DELETE when its already PURGE for instance. Concurrent
access to a mailbox should not be considered a rare or extranous
condition.
Of course if it kills performance that is another matter. But postgres is
just very bad at these kinds of queries. We had the same problem some time
ago with MAX() calls in some queries I don't remember now.
I'm leaving this bug open until it's actually fixed in the code...
----------------------------------------------------------------------
aaron - 25-Mar-06 10:41
----------------------------------------------------------------------
What did we need to fix here?
----------------------------------------------------------------------
aaron - 30-Apr-06 00:49
----------------------------------------------------------------------
This appears to have been fixed since time immemorial.
Issue History
Date Modified Username Field Change
======================================================================
25-Oct-05 18:44 davel New Issue
25-Oct-05 18:44 davel File Added: dbmail-query-opt.txt
26-Oct-05 09:00 paul Note Added: 0000947
26-Oct-05 16:28 davel Note Added: 0000949
26-Oct-05 21:58 paul Note Added: 0000950
26-Oct-05 21:58 paul Status new => assigned
26-Oct-05 21:58 paul Assigned To => paul
25-Mar-06 10:41 aaron Note Added: 0001063
30-Apr-06 00:49 aaron Status assigned => resolved
30-Apr-06 00:49 aaron Resolution open => fixed
30-Apr-06 00:49 aaron Note Added: 0001124
======================================================================
