Multicolumn indexes are most useful where constraints are on the left
most column, for the other columns the whole index needs to be scanned
and might result in the query planner choosing a sequential table scan.
If this is only an occasional query then performance might be
acceptable, else you get better performance with an index that matches
the query. As inserts need to update all the indexes performance will
be slightly slower. It's often a balance, in my case adding the index
was a good choice.
http://www.postgresql.org/docs/9.2/static/indexes-multicolumn.html
On 24/10/2013 14:03, Paul J Stevens wrote:
I believe you but there is already a composite primary key on that
table which includes all fields mentioned by Alan.
I assumed that would be enough.
On 15-10-13 12:08, Thomas Raschbacher wrote:
Thanks I thought it might be missing too but wasn'T sure as I didn'T
check the mysql/oracle ones.
@Paul: in this case it might be worth adding those to the create/migrate
scripts speed things up?
Regards
On 2013-10-15 11:25, Alan Hicks wrote:
You are missing three indexes, they are in the MySQL and Oracle create
scripts.
CREATE INDEX dbmail_header_headername_id_key on
dbmail_header(headername_id);
CREATE INDEX dbmail_header_headervalue_id_key on
dbmail_header(headervalue_id);
CREATE INDEX dbmail_header_physmessage_id_key on
dbmail_header(physmessage_id);
As soon as I added them the queries were sub second on my system.
Alan
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
