0

Suppose I have a not trivial query to return a list of places from a given name piece e.g. "Rio" would return both "Rio de Janeiro" and "Rio Negro". But in one place I would need a short version like "Rio Negro/BR" while in other I would have "Rio Negro (Gazetteer, Amazonas, Brasil)".

What is the optimal solution in PostgreSQL: create two smaller functions or one bigger?

The bigger function would be called from two different sources, each time running one of two similar SQL requests, both large.

Maybe if I split it in two different functions it will become faster?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rodrigo
  • 4,706
  • 6
  • 51
  • 94
  • What's the parameter you would add to the function ? just a boolean or something more meaningfull ? – Asoub Mar 08 '16 at 13:07
  • If I need to choose between only two options, it may be a boolean. But for the future I might have more than two, then I would use a short int or a char. – Rodrigo Mar 08 '16 at 13:09

1 Answers1

0

Two specialized functions are typically faster than one combined function if you just have to call one of them. If you'd have to call both specialized functions, it's the other way round: then it's typically faster to execute a single combined function. It depends on your actual use case.

There might be rare corner cases, for instance if the function is called only a few times per session. Then it might turn out to be faster to reuse the same combined function for small functions with saved query plans. (PL/pgSQL functions re-use query plans within the same session. SQL functions don't). The overhead might be more expensive for two separate functions. But that's an academic exception. Related:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228