Change GUC parameter before subquery PostgreSQL

Question

I'm currently doing a query like such:

SELECT * FROM (
    (SELECT * from A WHERE first_name % 'fakeFirstName') 
    UNION 
    (SELECT * from B WHERE last_name % 'fakeLastName')
) AS result;

Both A and B are views of the same underlying table C, with the exact same columns.

However the % operator uses the GUC parameter pg_trgm.similarity_threshold to compute both first_name % 'fakeFirstName' and last_name % 'fakeLastName', and my goal is to change this parameter before each sub query since the similarity thresholds are different for these two column.

To change the pg_trgm.similarity_threshold parameter, for example at the value 0.2, I have two options:

SET pg_trgm.similarity_threshold = 0.2;
SELECT set_limit(0.2);

I am very concerned with speed of execution, which means I'd prefer to use the % operation with a GIN index instead of the <-> operator with a GIST index.

I tried to do something like the following, but it didn't work since the function set_limit() wasn't called before the use of the % operator:

SELECT * FROM (
    (SELECT *, set_limit(0.2) from A WHERE first_name % 'fakeFirstName') 
    UNION 
    (SELECT *, set_limit(0.6) from B WHERE last_name % 'fakeLastName')
) AS result;

Any help is deeply appreciated.

Thank you for your edit a_horse_with_no_name, i'm not used to posting on stackoverflow ! — Olivier Nappert

Vao Tsun Vao Tsun · Accepted Answer · 2018-04-20T12:39:53

I'm not sure, if I understand you correctly, but you can try joining A and B against CTE with set_limit(), eg:

t=# with a as (select set_limit(0.21))
, b as (select set_limit(0.22))
select show_limit(), 'a' from pg_database where datname = 't'
union
select show_limit(), 'b' from pg_database join a on true where datname = 't'
union
select show_limit(), 'c' from pg_database join b on true where datname = 't'
t-# order by 2;
 show_limit | ?column?
------------+----------
        0.2 | a
       0.21 | b
       0.22 | c
(3 rows)

also mind you don't change it back, so the value remains the last called set_limit()

Change GUC parameter before subquery PostgreSQL

1 Answers