Michael,
> I just found in the postgres config:
> #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
># and their durations, in milliseconds.
>
> Looks like that should be enough for a query log. Would I still need
> log_statement=all,
No.
> or is =ddl enoug
On Mittwoch, 9. Juli 2008 Josh Berkus wrote:
> On PostgreSQL, the way you do this is by adding log_statement and
> log_duration options, which will add queries and their execution
> times to the activity log.
I just found in the postgres config:
#log_min_duration_statement = -1 # -1 is disabled, 0
Michael,
It could be we can find other optimzations as well. Paul, is there a
simple way to log all DB queries to a separate file? Like this, we
could log for a typical day, and then see which are the most queries
and where we could start to optimize. I know you, Paul, have other
things to do
in this case you aren't matching on a blob because the encode function
translates it to text, so you are actually matching on text.
> I guess things have changed in pg since I first wrote those queries. I
> distinctly remember having to use HAVING + GROUP BY to be able to match
> on blobs. Or may
Nobody commented on the fact that for a body text search it keeps on
spawning new search threads which slowly bring the machine to its knees.
Does this make any sense?
Sim
Sim Zacks wrote:
> DBMail 2.2.10
> Postgresql 8.2.7
> Thunderbird 2.0.0.14
>
> When searching in the body text for sim, th
Michael Monnerie wrote:
> On Mittwoch, 9. Juli 2008 Sim Zacks wrote:
>> The original query:
>> SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
>> dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages
>> m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status
On Mittwoch, 9. Juli 2008 Sim Zacks wrote:
> The original query:
> SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
> dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_messages
> m ON p.id = m.physmessage_id WHERE mailbox_idnr = 8 AND status IN
> (0,1 ) AND k.is_header =
At least in postgresql 8.2.7 it works fine, also returning the
messageblk. I got rid of the group by also, because I couldn't think of
a case where it would be necessary.
The original query:
SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id
Paul,
2) Wouldn't the query be much faster if the
ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' was in the WHERE
clause instead of the HAVING clause?
Try it. Afaik, it simply wont work.
That depends on whether you're trying to return just a list of IDs, or
the full contents of the messa
Paul,
> Very good question. The only solution I can think of is full text indexing.
I'll take a stab at this once I have my server up and running. Necessarily,
though, the solution will be database-specific.
--
Josh Berkus
PostgreSQL
San Francisco
_
I did try it and it worked fine. Slightly faster.
Also there is no reason for the group by because the query isstructured
so that there is always 1 result, unless there are really multiple
identical messageblks for the same message.
I tried it on a mailbox without that many messages, just for te
Sim Zacks wrote:
> 2) Wouldn't the query be much faster if the
> ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' was in the WHERE
> clause instead of the HAVING clause?
Try it. Afaik, it simply wont work.
> 3) Does anybody have imap search working on some reasonable level?
Very good question.
DBMail 2.2.10
Postgresql 8.2.7
Thunderbird 2.0.0.14
When searching in the body text for sim, the imap reaction became very
slow. We checked the server and it is using 80 percent CPU.
I checked pg_stat_activity and found 30 rows with the exact same
current_query:
"SELECT m.message_idnr,k.messageb
13 matches
Mail list logo
