Will Postgres cache a SIMILARITY (or any expensive function call) result if I use it more than once in a query?

user_3968048 0
Viewed 42

Let's say I do something like this:

    SELECT c."id", ..., SIMILARITY(c."body", $1) as "similarity"
    FROM "comments" AS c
    WHERE SIMILARITY(c."body", $1) > 0.2
    ORDER BY SIMILARITY(c."body", $1) DESC
    LIMIT 10

I make the exact same SIMILARITY(c."body", $1) call 3 times, which is the most expensive part of the query. Will Postgres perform that calculation 3 times? Or will it notice that it's already done it and cache it?

postgresql
1 Answers

Yes, the function could be called several times.

Use a subquery to avoid that:

SELECT c.id,
       similarity
FROM (SELECT id,
             SIMILARITY(c."body", $1) AS similariity
      FROM comments) AS c
WHERE similarity > 0.2
ORDER BY similarity DESC
LIMIT 10;
user_64643080
Related