IdentifierFactoryImpl.findByURI() is getting a full table scan instead of index scan

[chrismccracken]

At https://github.com/dotCMS/core/blob/master-3.2/src/com/dotmarketing/business/IdentifierFactoryImpl.java#L173, the lower() SQL function is causing the sql index to be unused for this query, which is very expensive on large identifier tables.

On postgres, an expressional index (http://www.postgresql.org/docs/9.3/static/indexes-expressional.html) such as this fixes it:
create index identifier_lcase_parent_path_asset_name_host_inode on identifier ( lower(parent_path), lower(asset_name), host_inode );

0.233 ms to 0.085 ms speedup, on demo. https://gist.github.com/chrismccracken/a563d419464838a41d82

[maurizo]

This approach will only work on Postgresql and Oracle. Neither MySQL, MSSQL or H2 allows expressions (for the LOWER function) as part of the index definition.

[brentgriffin]

Seems that we may need to add a column that is auto set to be the lower(of parent_path + asset_name), create an index on this column, and then change the query to use the newly created column.

[jgambarios]

@jtesser thoughts?

[jtesser]

Yes I think we need to hold until 3.3 on this one

[jcastro-dotcms]

PR: #11385

[bryanboza]

Fixed, tested on master // postgres // FF