How to speed up postgres cursor

DB‎ > ‎Postgres‎ > ‎

How to speed up postgres cursor

Thank you for visiting this page, this page has been update in another link How to speed up postgres cursor

This is a big title which already brought a lot of discussions over decades. I'm not going to go through everything but few tips which discouraged lots of people from using it.

#1. Most of people use cursor in function or sorts of stored procedure wirtten in PL/pgSQL (LANGUAGE plpgsql). In any case, they execute queries like prepared statements , so you should be aware that the cursor slowness could be from the function or stored procedure, other than cursor itself. The same thing happens for regular SQL.

See my another link for more detail about it.
https://sites.google.com/site/itmyshare/database-tips-and-examples/sql-slow-in-postgresql-function

#2. use unbound cursor if you have input parameters and parameters are more likely distributed widely over a table/index.

The reason is similar, is to make a new query plan for each SQL according to its input, it adds a bit overhead, but worse to do it for long run.

#3. Be careful with hold option for cursor, I don't know why it can't be done in application level.

#4. Close cursor as soon as possible, especially if you use cursor for update.

Nothing better than example, here is one.

CREATE OR REPLACE FUNCTION f_insertacl() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    msk INTEGER;
    flag INTEGER;
    rstype INTEGER;
    id character(36);
    parentid character(36);
    r_t_acl t_acl%ROWTYPE;
    t_acl_curs refcursor;

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;

            open t_acl_curs for execute 'select * from t_acl where rs_id=' || quote_literal(parentid) || ' order by ace_order' ;

            FETCH NEXT from t_acl_curs INTO r_t_acl;
            WHILE (FOUND) LOOP
              r_t_acl.rs_id :=id;
              r_t_acl.rs_type :=rstype;
              IF (r_t_acl.flags & flag ) > 0 THEN
                r_t_acl.flags := (r_t_acl.flags | msk) # msk;
                r_t_acl.ace_order :=nextval('serial');

                INSERT into t_acl values(r_t_acl.rs_id,r_t_acl.rs_type,r_t_acl.type,r_t_acl.flags,r_t_acl.access_msk,r_t_acl.who,r_t_acl.who_id,r_t_acl.address_msk,r_t_acl.ace_order);

              END IF;
              FETCH NEXT from t_acl_curs INTO r_t_acl;
            END LOOP;
        END IF;
    END IF;
    RETURN NULL;
END;
$$;

You may realized that it's a similar one I used in
https://sites.google.com/site/itmyshare/database-tips-and-examples/sql-slow-in-postgresql-function

Yes, it does the same function as the old one in the article, but using cursor, which is much faster.

Make a comment or drop me an e-mail if you have further question.

Comments