CODEWITHSUNDEEP
postgresqlperformance
jsindos
jsindos

Reputation: 539

If I call the same postgres function with the same arguments twice in a query, does it calculate once or twice?

If I make the following query in postgres, is it calculating the ts_rank twice or just once? If it is calculating it twice, is it possible to make it calculate it only once?

SELECT id, name, "createdAt", price, ts_rank(document, to_tsquery(:query)) AS rank
FROM search_index
WHERE document @@ to_tsquery(:query)
ORDER BY ts_rank(document, to_tsquery(:query)) DESC;

Upvotes: 0

Views: 357

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

In this case, it should be calculated only once time. Postgres detects equal expressions. Generally, if you afraid about this, then you can calculate expression in subquery.

Some like:

SELECT c1, c1 FROM (SELECT exp AS c1) s;

The function to_tsquery() is very expensive without fulltext index. if you have fulltext index, and if there are only one hundreds selected records, then overhead of ts_rank should not be significant.

Upvotes: 1

Related Questions