Adding indexes to tables of Apache James 3
TweetPosted on Sunday Nov 15, 2015 at 05:06PM in Technology
I’m using Apache James (3.0-beta5-SNAPSHOT) as the mail server for my domain. It’s running with JPA backend on PostgreSQL.
Today I’ve found that some slow queries are logged as the number of emails has been grown through the days. The slowest two are following:
-
SELECT t0.property_id, t0.property_line_number, t0.property_local_name, t0.property_name_space, t0.property_value FROM public.JAMES_MAIL_PROPERTY t0 WHERE t0.mailbox_id = $1 AND t0.mail_uid = $2 ORDER BY t0.property_line_number ASC -
SELECT t0.userflag_id, t0.userflag_name FROM public.JAMES_MAIL_USERFLAG t0 WHERE t0.mailbox_id = $1 AND t0.mail_uid = $2 ORDER BY t0.userflag_id ASC
These queries are used sequential scan. It’s terrible for thousands of rows so I’ve created a couple of indexes to avoid sequential scan as follows:
-
create index on james_mail_property (mailbox_id, mail_uid); -
create index on james_mail_userflag (mailbox_id, mail_uid);
They seems work expectedly as I’ve confirmed that now these queries are using index scan instead.
Tags: james postgresql
