Using functions on columns instead on literals

I always hear from developers that DBAs are against the use of ORMs (like Hibernate) without any reason. Before being a DBA, I was a developer and I recognize that I like to use ORMs because it makes it easier but you should always have in mind what’s beyond the ORM’s classes and what queries are executed by the ORM.

In other words: to know how the ORM works. Of course it can be some differences when running the application on SQL Server, PostgreSQL or Oracle but usually not a lot.

The following example is on a slow query I found through pg_stat_statements, in PostgreSQL, caused by the use of the upper function because PostgreSQL is case sensitive. In SQL Server this specific problem could be avoided if the database is created using a case insensitive collation. But other examples could be found that use functions on columns.

The query is against a table called BinaryFile where are stored the path (column named ruta) in the file system where files are placed. The table has 181.438 rows. There’s a normal index on ruta: