I have PostgreSQL 9.1.1 running in a shared hosting environment and I'm running this query:
SELECT a.id, CASE WHEN a.idpai IS NULL THEN a.nome ELSE concat(a.nome, ' (', b.nome, ')') END AS nome, a.idpai FROM localidade a LEFT OUTER JOIN localidade b ON a.idpai = b.id WHERE a.idcidade = :idcidade AND normalizar( CASE WHEN a.idpai IS NULL THEN a.nome ELSE concat(a.nome, ' (', b.nome, ')') END ) LIKE normalizar(:nome) ORDER BY CASE WHEN normalizar( CASE WHEN a.idpai IS NULL THEN a.nome ELSE concat(a.nome, ' (', b.nome, ')') END ) LIKE substring(normalizar(:nome) from 2) THEN 1 ELSE 2 END, a.nome, b.nome LIMIT 15
The performance is pretty good, the query returns in less than 50ms. Though, I'm repeating this part 3 times inside the query:
CASE WHEN a.idpai IS NULL THEN a.nome ELSE concat(a.nome, ' (', b.nome, ')') END
I believe this reduces the maintainability of the query and is not DRY at all. What is the recommended way to handle that? Should I create a function in my schema solely for this query? Or just store it in a variable and concatenate into the query in my back end before sending it over to the db server? Or is there any better way around?
I believe what this query does isn't the main point here, but just to give some background:
localidade table may or may not have a relation with itself through the
idpai which references a "parent" register. Assuming this table format:
id | nome | idpai 1 | foo | 2 | bar | 1
So if the query placeholder
%foo%, it will return:
id | nome | idpai 1 | foo | 2 | bar (foo) | 1
ORDER BY clause is just to display results which the query parameter matches the beginning of the returned row's
name before those who don't -
SUBSTRING('%foo%' FROM 2) returns
foo% -, and then the matching and not matching groups are ordered by
b.nome ASC as the SQL shows.
normalizar is a
STABLE function that takes a string as parameter and calls
LOWER() and removes accentuation returning the new string, so I can perform case-insensitive and accent-insensitive string comparison. Similar to
unaccent which I couldn't properly apply on my use case due to some encoding problems (page is UTF8 and DB is latin1, if I convert the UTF8 to latin1 solely on the back end I'll have broken UTF8 pages; well this has been solved with the function above and is off-topic).
idcidade is just a foreign key which I use to reduce the result set to less than 0.1% of the table. Hence performance isn't really an issue in this specific case.
My real question is, does calling
CASE multiple times passing the exact same parameters have an impact on performance? I assume the result from
CASE should be
STABLE and the query will be optimized automatically then. If that's the case, I can store part of the query into a variable on my back end and build the query string using that. Otherwise, if calling
CASE multiple times in the same query with the same arguments affects performance, should I create a
STABLE function for it then or what's the correct approach to this? Any pointers are appreciated.