Postgresql Slow on custom function, php but fast if directly input on psql using text search with gin index

Question

I have 3 tables Person, Names, and Notes. Each person has multiple name and has optional notes. I have full text search on some columns on names and notes (see below), they are working perfectly if the word i search with is in the result set or is in the db, this is for custom function,php, and psql. The problem now is that when the word i search is not present in the db the query gets super slow in php and custom function but still fast on psql. on psql its less than 1s others are more than 10s.

Tables:
Person | id, birthday
Name   | person_id, name, fs_name
Notes   | person_id, note, fs_note

Beside PK and FK index, Gin index on fs_name and fs_note.

Function/Query
create or replace function queryNameFunc (TEXT) returns TABLE(id int, name TEXT) as $$
               select id, name from person_name pnr
               inner join person pr on (pnr.person_id=pr.id)
               left join personal_notes psr on (psr.person_id = pr.id)
               where pr.id in
                        (select distinct(id) from person_name pn
                        inner join person p on (p.id = pn.person_id)
                        left join personal_notes ps on (ps.person_id = p.id)
                        where tname @@ to_tsquery($1)
                        limit 20);
$$ language SQL;

The where condition is trimmed down in here, so for example if i do 'john & james' on $1 and the data is on the db then results is fast but if 'john and james' are not in db then its slow. This got slower as i have 1M records on person and 3M+ on names (all dummy records). Any idea on how to fix this? I tried restarting the server, restarting the postgresql.

Answer 1

The database has to preprare the inner query before it has any knowledge about the parameter. This might result in a bad queryplan. To avoid this problem in a function, use the plpgsql-language and use EXECUTE inside the function:

CREATE OR REPLACE FUNCTION queryNameFunc (TEXT) RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY EXECUTE '
        SELECT 
            id, 
            name 
        FROM 
            person_name pnr
                INNER JOIN person pr ON (pnr.person_id=pr.id)
                LEFT JOIN personal_notes psr ON (psr.person_id = pr.id)
               WHERE 
            pr.id IN(
                SELECT 
                    DISTINCT(id) 
                FROM 
                    person_name pn
                        INNER JOIN person p ON (p.id = pn.person_id)
                        LEFT JOIN personal_notes ps ON (ps.person_id = p.id)
                        WHERE tname @@ to_tsquery($1)
                        LIMIT 20)' USING $1;
END;
$$ LANGUAGE plpgsql;

This works in version 8.4 and you do have to install plpgsql:

CREATE LANGUAGE plpgsql;