Affects Version/s: 6.1.0 CE GA1, 6.1.10 EE GA1, 6.2.0 CE B3
Environment:Tomcat 7 + PostgreSQL 9.1. Portal 6.1.x EE GIT ID: d8c16ee8eee29699cd4d3d2a2f596dbe9aea67ea.
Tomcat 7 + PostgreSQL 9.1. Portal 6.2.x CE GIT ID: 8f35f9c075878f7b1769c05d2f8c7487e8c3e7c7.
When a custom plugin is using ServiceBuilder, custom database tables are automatically upgraded during deploy (if they changed since last deploy). But such upgrade never recreates database indexes on those custom tables (usually located in WEB-INF/sql/indexes.sql).
The responsible code goes through PluginPackageHotDeployListener.doInvokeDeploy() -> initServiceComponent() -> ServiceComponentLocalServiceImpl.initServiceComponent() -> upgradeDB() -> doUpgradeDB() ... -> upgradeModels().
For every entity in plugin, upgradeModels() backs up the DB table into temp file, then drops the table from DB and creates it based on new createSQL (this is taken from static field on XXXModelImpl class). Indexes defined in WEB-INF/sql/indexes.sql are never created again.
This can of course cause major performance issues, when DB tables grow in size and ServiceBuilder generated DB queries cannot use database indexes.
1a - Use this sample portlet project.
1b - Alternatively you can create an emptry portlet with the following service.xml.
2 - Build.
3 - Deploy the portlet to a Liferay.
4 - Check table hd_test; you'll see that there is an index ix_33e60be7 that holds companyId and userId.
5 - Edit the service.xml and comment out the following or remove it completely.
6 - Perform steps 2,3,4 again.
You'll see that the index has been removed from the database. Actually table hd_test has been dropped and re-created, but its index ix_33e60be7 has not.