I am querying a WFS housed in geoserver - the data itself lives in a postgresql database. I would like to run case insensitive queries against the WFS and see there is an option to specify strToLowerCase on the field I am querying, but it doesn't appear to take advantage of the lowercase index I have created in postgresql. The table I am querying has several million records, and when using the strToLowerCase I run into memory issues. Is there a way I can do a case insensitive query on the data in the WFS and take advantage of the lowercase index that exists?
Looking at the GeoTools source code (
FilterTOSqlHelper) it looks like your
strToLower should be converted into a postgresql
lower function and passed to the database.
So, I would turn the logging level upto GeoTools Debug and check that the SQL query really does have
lower in it (if not raise a bug). Then paste that into pgadmin and do an explain on it to check if the index is being used.
You might also try the tip I give in this blog post to speed up case insensitive like queries.