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

Question

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;

Depends on whether the function is marked as immutable, stable, or volatile, and what the predicted cost is. — Bergi
– Bergi, Commented Aug 15, 2021 at 3:20

Pavel Stehule · Accepted Answer · 2021-08-15 03:21:08Z

1

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.

answered Aug 15, 2021 at 3:21

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jsindos Over a year ago

Thanks for the additional info, I have set up a fulltext index using CREATE INDEX idx_fts_search ON search_index USING gin(document);.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related