On Sun, Jun 15, 2008 at 11:03 AM, John Fawcett <[EMAIL PROTECTED]> wrote:
> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Yavor Shahpasov wrote: > > | A005 SEARCH CHARSET UTF-8 TEXT "charsettest" 1.. > | > | On my system this command would take 40-50 seconds. After resolving the > | above issue it went down to 12-15 seconds. > | I have traced this command to the following SQL query > | > | > | SELECT message_idnr > | FROM > | dbmail_messages m > | JOIN dbmail_physmessage p ON m.physmessage_id=p.id <http://p.id> > | JOIN dbmail_headervalue v on v.physmessage_id=p.id <http://p.id> > | WHERE > | mailbox_idnr = 1 > | AND status IN (0,1) > | AND SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY > | message_idnr > | > | > | The search is done on a mailbox with 2k emails. These span around 45k > | rows in dbmail_headervalue. I have tried modifying it to get better > | performance but with no results. From my experiance with traditional > | Maildir based imap server searching through 2000 emails is much faster, > | given that the above searches only in the headers. Also it looks like > | the index on "SUBSTRING(headervalue,0,255)" is completly ignored for the > | ILIKE operator. > | > | I don't know ho the above can be improved, if anyone has ideas let me > know. > | > Yavor, > > have you analyzed dbmail_message and dbmail_physmessage ? If the > statistics were old for dbmail_headervalue, they could also be for > the other tables. > > I've not used postgres so I am not familiar with db maintenance tasks, > but I imagine with your volumes you will need to run analzye > periodically otherwise stats will get out of sync. > > On the query above, what does an explain show? > > John > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v2.0.4-svn0 (GNU/Linux) > Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org > > iD8DBQFIVMzmd4I3jTtt9EIRAiVJAJ92UMCNkx3dFNr0Ne3xu4FRFrE2uwCg1JVI > hQzBSpzIRcarnYs6pMalhVs= > =AOGq > -----END PGP SIGNATURE----- > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > John, Yes the table is analyzed (Several times). I also had to modify the statistics for dbmail_headervalue in order to get it to use the index on physmessage_id and headername_id alter table dbmail_headervalue alter column physmessage_id set statistics 100; alter table dbmail_headervalue alter column headername_id set statistics 100; http://developer.postgresql.org/pgdocs/postgres/planner-stats.html Here are the explain plans from my production system. dbmail=> SELECT message_idnr FROM dbmail_messages m JOIN dbmail_physmessage p ON dbmail-> m.physmessage_id=p.id JOIN dbmail_headervalue v on v.physmessage_id=p.id WHERE mailbox_idnr = 1 AND status IN (0,1) AND dbmail-> SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY message_idnr dbmail-> ; message_idnr -------------- (0 rows) dbmail=> EXPLAIN dbmail-> SELECT message_idnr FROM dbmail_messages m JOIN dbmail_physmessage p ON dbmail-> m.physmessage_id=p.id JOIN dbmail_headervalue v on v.physmessage_id=p.id WHERE mailbox_idnr = 1 AND status IN (0,1) AND dbmail-> SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY message_idnr dbmail-> ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Sort (cost=90639.23..90639.24 rows=1 width=8) Sort Key: m.message_idnr -> Nested Loop (cost=0.00..90639.22 rows=1 width=8) -> Nested Loop (cost=0.00..90629.55 rows=1 width=16) -> Seq Scan on dbmail_headervalue v (cost=0.00..90624.13 rows=1 width=8) Filter: ("substring"(headervalue, 0, 255) ~~* '%charsettest%'::text) -> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage p (cost=0.00..5.41 rows=1 width=8) Index Cond: ("outer".physmessage_id = p.id) -> Index Scan using dbmail_messages_2 on dbmail_messages m (cost=0.00..9.65 rows=1 width=16) Index Cond: ("outer".physmessage_id = m.physmessage_id) Filter: ((mailbox_idnr = 1) AND ((status = 0) OR (status = 1))) (11 rows) dbmail=> EXPLAIN ANALYZE dbmail-> SELECT message_idnr FROM dbmail_messages m JOIN dbmail_physmessage p ON dbmail-> m.physmessage_id=p.id JOIN dbmail_headervalue v on v.physmessage_id=p.id WHERE mailbox_idnr = 1 AND status IN (0,1) AND dbmail-> SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY message_idnr dbmail-> ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=90639.23..90639.24 rows=1 width=8) (actual time=13920.019..13920.019 rows=0 loops=1) Sort Key: m.message_idnr -> Nested Loop (cost=0.00..90639.22 rows=1 width=8) (actual time=13920.008..13920.008 rows=0 loops=1) -> Nested Loop (cost=0.00..90629.55 rows=1 width=16) (actual time=13920.006..13920.006 rows=0 loops=1) -> Seq Scan on dbmail_headervalue v (cost=0.00..90624.13 rows=1 width=8) (actual time=13920.003..13920.003 rows=0 loops=1) Filter: ("substring"(headervalue, 0, 255) ~~* '%charsettest%'::text) -> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage p (cost=0.00..5.41 rows=1 width=8) (never executed) Index Cond: ("outer".physmessage_id = p.id) -> Index Scan using dbmail_messages_2 on dbmail_messages m (cost=0.00..9.65 rows=1 width=16) (never executed) Index Cond: ("outer".physmessage_id = m.physmessage_id) Filter: ((mailbox_idnr = 1) AND ((status = 0) OR (status = 1))) Total runtime: 13920.102 ms (12 rows) -- Yavor Shahpasov yavosh.at.gmail.com "simplicity and flexibility beat optimization and power in a world where connectivity is key" Adam Bosworth http://www.flickr.com/photos/yavo/4310154/
_______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
