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 IN >> (0,1 ) AND k.is_header = '0' GROUP BY m.message_idnr,k.messageblk >> HAVING ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' > > EXPLAIN ... gives: > HashAggregate (cost=266.97..266.98 rows=1 width=797) > >> My 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 = '0' and >> ENCODE(k.messageblk::bytea,'escape') LIKE '%sim%' > > EXPLAIN ... gives: > Nested Loop (cost=2.11..266.96 rows=1 width=797) > > So the 2nd form can be much faster, and only in worst case takes as long > as the 1st form. Pure improvement.
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 maybe my memory is failing and it was mysql that had such a requirement... > > 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 - so a query log would be nice that we can share here, and > let the community optimize it. I'm sure there are some DB gurus on this > list :-) I always use trace_stderr=5 to generate a logfile with full queries. The syslog traces are truncated, but the stderr output is not. Of course, in 2.3 with prepared statements things are a bit more tricky, but for 2.2 it should work just fine. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
