-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
| John,
| 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 <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
| dbmail-> SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY
| message_idnr
| dbmail-> ;
| message_idnr
| --------------
| (0 rows)
| dbmail=> EXPLAIN ANALYZE
| dbmail-> SELECT message_idnr FROM dbmail_messages m JOIN
| dbmail_physmessage p ON
| dbmail-> 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
| 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
| <http://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
I can't see anything that looks strange about this except the time taken
for the scan on dbmail_headervalue.
It would be interesting to see what the performance is like of this
query on other people's systems that have millions of rows in the
headervalue table, to see if it's a general problem.
Sorry not be be more helpful.
John
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
iEYEARECAAYFAkhlQ7YACgkQd4I3jTtt9EKutACfcOXA+wdHB6/OgWd/SWTFqMbz
zgMAoMNlhv4MNxp8KqBActEufL52L8eE
=hXbd
-----END PGP SIGNATURE-----
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
