Have you experienced SQL slower in postgreSQL function?  The same SQL is much slower than in command line? I do.

Usually I don't like to cite/post theory in my particle, but this time, it worth than show you lines and lines query plan.

Functions with LANGUAGE SQL are basically just batch files with plain SQL commands in a function wrapper accepting parameters. The  PL/pgSQL (LANGUAGE plpgsql) is mature. It works well and has been improved with every release over the last decade, but it serves best as glue for SQL commands. It is not meant for heavy computations (other than with SQL commands).

Functions in PostgreSQL execute queries like prepared statements, which cuts off some of the overhead and make them generally faster that the equivalent SQL statements. This may be a noticeable effect depending on circumstances.

This carries the advantages and disadvantages of prepared statement - as described in the linked manual page. In the case of queries with very uneven data distribution and varying parameters and results it may be of advantage to use dynamic SQL with EXECUTE, because the gain from an optimized execution plan is bigger than the loss due to re-planning every time.

PostgreSQL 9.2 brought a major improvement in this area: The planner now plans the query at execution and decides whether it could be worth to replan with the current parameter values. So you get the best of both worlds, performance-wise, and you don't have to (ab)use EXECUTE for this purpose any more. Details in What's new in PostgreSQL 9.2 of the PostgreSQL Wiki.

But, really? maybe for most of simple cases.

Here are some tips from me.

Recently I had to fight with a complicated function triggered by insert action. It has a query and a insert action with filters, two tables involved, one has 30M records, the other has 17M records.

In the function, The query intended to run sequence scan instead of index scan, it becomes a bit better after I upgraded to 9.2 which has indexscanonly feature, and switching off enable_seqscan doesn't help much either.

So, first, I added the following line to the begening of the function.

    SET enable_seqscan TO false;

It made the function to finish in 4 secs instead of 22.

Then, I decided to use EXECUTE on each QUERY, then it made the function to finish in 4ms. What a great gain!!!


Too much talking, check the function here, not from me, from an application which I work with.

Old function:

CREATE OR REPLACE FUNCTION f_insertacl() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    msk INTEGER;
    flag INTEGER;
    rstype INTEGER;
    id character(36);
    parentid character(36);

BEGIN
    IF (TG_OP = 'INSERT') THEN
        msk := 0;
        SELECT INTO rstype itype FROM t_inodes WHERE ipnfsid = NEW.ipnfsid;

        IF rstype = 32768  THEN
            id := NEW.ipnfsid;
            parentid := NEW.iparent;
            rstype := 1;    -- inserted object is a file
            flag := 1;      -- check flags for 'f' bit
            msk := 11;      -- mask contains 'o','d' and 'f' bits

        ELSIF (rstype = 16384 AND NEW.iname = '..') THEN
            id := NEW.iparent;
            parentid := NEW.ipnfsid;
            rstype := 0;    -- inserted object is a directory
            flag := 3;      -- check flags for 'd' and 'f' bits
            msk := 8;       -- mask contains 'o' bit
        END IF;

        IF msk > 0 THEN
            ALTER SEQUENCE serial START 0;

            INSERT INTO t_acl
            SELECT id, rstype, type, (flags | msk) # msk, access_msk, who, who_id, address_msk, nextval('serial')
            FROM t_acl
            WHERE  rs_id = parentid AND (flags & flag > 0)
            ORDER BY ace_order;
        END IF;
    END IF;
    RETURN NULL;
END;
$$;


New function:


CREATE OR REPLACE FUNCTION f_insertacl() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    msk INTEGER;
    flag INTEGER;
    rstype INTEGER;
    id character(36);
    parentid character(36);

BEGIN
    SET enable_seqscan TO false;
    IF (TG_OP = 'INSERT') THEN
        msk := 0;
        EXECUTE 'SELECT itype FROM t_inodes WHERE ipnfsid = ' || quote_literal(NEW.ipnfsid)  INTO rstype;

        IF rstype = 32768  THEN
            id := NEW.ipnfsid;
            parentid := NEW.iparent;
            rstype := 1;    -- inserted object is a file
            flag := 1;      -- check flags for 'f' bit
            msk := 11;      -- mask contains 'o','d' and 'f' bits

        ELSIF (rstype = 16384 AND NEW.iname = '..') THEN
            id := NEW.iparent;
            parentid := NEW.ipnfsid;
            rstype := 0;    -- inserted object is a directory
            flag := 3;      -- check flags for 'd' and 'f' bits
            msk := 8;       -- mask contains 'o' bit
        END IF;

        IF msk > 0 THEN
            ALTER SEQUENCE serial START 0;

            execute 'INSERT INTO t_acl
            SELECT' || quote_literal(id) || ', ' || rstype || ', type, (flags | ' || msk || ') , access_msk, who, who_id, address_msk, ace_order
            FROM t_acl
            WHERE  rs_id = ' || quote_literal(parentid) || ' AND (flags & ' || flag || ' > 0)
            ORDER BY ace_order';
        END IF;
    END IF;
    RETURN NULL;
END;
$$;