PostgreSQL simple query with repeated function calls

Question

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:

The 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 :nome contains %foo%, it will return:

id | nome       | idpai
 1 | foo        |
 2 | bar (foo)  |   1

The 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 a.nome and 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.

Answer 1

A few additional simplifications:

SELECT a.id
      ,concat(a.nome, ' (' || b.nome || ')') AS nome
      ,a.idpai
FROM   localidade a
LEFT   JOIN localidade b ON a.idpai = b.id
WHERE  a.idcidade = :idcidade
AND    normalizar(concat(a.nome, ' (' || b.nome || ')'))
       LIKE ('%' || normalizar(:nome) || '%')
ORDER  BY (normalizar(concat(a.nome, ' (' || b.nome || ')'))
           LIKE (normalizar(:nome))  || '%') DESC NULLS LAST
       ,2
LIMIT  15;

• right(x, -1) would be a little simpler and faster than substring(x from 2). But if you follow my advice below you don't need either.

• If you use this query in a prepared statement of function, and you know the search term is never anchored (always % around the term - %foo%), then it will be faster to use explicit placeholder. Let the planner know, the pattern always starts with %, so the query plan can be optimized.

• You can just ORDER BY the boolean result of an expression. No need for a case statement. FALSE (0) sorts before TRUE (1) sorts before NULL.
  I added NULLS LAST to get 100% identical behaviour like your original with descending order. Consider this SQLfiddle demonstrating the effect.

• I also ORDER BY the positional parameter 2 to simplify the syntax. Won't make the query faster, just shorter.

• One thing to consider: If a.idpai IS NOT NULL that doesn't necessarily mean b.nome IS NOT NULL, so the original and the simplified concat() version are not identical if b.nome can be NULL.

• I would make the function normalizar IMMUTABLE, since the result is always the same for the same input. Probably won't make a difference for this query, though. Both STABLE and IMMUTABLE function can be optimized by the query planner.

• Some other minor syntax simplifications just to demonstrate possibilities. Like .. ~~ is a Postgres operator for the SQL standard LIKE. (Niether is faster, ~~ is just shorter but non-standard. Note the slight difference in operator precedence. You may have to change parenthesis since ~~ (or ~~*, !~~, !~~*) binds stronger than LIKE (or ILIKE, NOT LIKE, NOT ILIKE).

If your tables are big and you really want fast - like several orders of magnitude faster than what you have - then create a materialized view (read here and here) with a trigram GIN or GiST index to support non-anchored LIKE searches. Read here and here.

Answer 2

I think this part:

CASE WHEN a.idpai IS NULL THEN a.nome
ELSE concat(a.nome, ' (', b.nome, ')')
END AS nome

Can be replaced with:

CONCAT(a.nome,  ' (' || b.nome || ')') AS nome

If b.nome is NULL, then "' (' || b.nome || ')'" will also be NULL (concatenating strings using '||' with NULL values will result in NULL. CONCAT() ignore null values, so will return only a.none in this case

In other words: if b.nome is present, it will output "a.nome (b.nome)", otherwise "a.nome"

You can test it yourself, here's my sqlfiddle:

http://sqlfiddle.com/#!1/d41d8/744/0

Also, you can try to use a subselect to prevent having to repeat this CONCAT;

http://sqlfiddle.com/#!1/a9571/7/0

I haven checked if this improves performance, but it reduces the amount of repeated code and may be better maintainable