From: David Mitchell <david.mitchell@telogis.com>
To: pgsql-performance@postgresql.org
Subject: How can I speed up this function?
Date: Tue, 28 Jun 2005 14:37:34 +1200
Message-ID: <42C0B7EE.4040608@telogis.com> (raw)
We have the following function in our home grown mirroring package, but
it isn't running as fast as we would like. We need to select statements
from the pending_statement table, and we want to select all the
statements for a single transaction (pending_trans) in one go (that is,
we either select all the statements for a transaction, or none of them).
We select as many blocks of statements as it takes to top the 100
statement limit (so if the last transaction we pull has enough
statements to put our count at 110, we'll still take it, but then we're
done).
Here is our function:
CREATE OR REPLACE FUNCTION dbmirror.get_pending()
RETURNS SETOF dbmirror.pending_statement AS
$BODY$
DECLARE
count INT4;
transaction RECORD;
statement dbmirror.pending_statement;
BEGIN
count := 0;
FOR transaction IN SELECT t.trans_id as ID
FROM pending_trans AS t WHERE fetched = false
ORDER BY trans_id LIMIT 50
LOOP
update pending_trans set fetched = true where trans_id =
transaction.id;
FOR statement IN SELECT s.id, s.transaction_id, s.table_name, s.op,
s.data
FROM dbmirror.pending_statement AS s
WHERE s.transaction_id = transaction.id
ORDER BY s.id ASC
LOOP
count := count + 1;
RETURN NEXT statement;
END LOOP;
IF count > 100 THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Table Schemas:
CREATE TABLE dbmirror.pending_trans
(
trans_id oid NOT NULL,
fetched bool DEFAULT false,
CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
)
WITHOUT OIDS;
CREATE TABLE dbmirror.pending_statement
(
id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
transaction_id oid NOT NULL,
table_name text NOT NULL,
op char NOT NULL,
data text NOT NULL,
CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE UNIQUE INDEX idx_stmt_tran_id_id
ON dbmirror.pending_statement
USING btree
(transaction_id, id);
Postgres 8.0.1 on Linux.
Any Help would be greatly appreciated.
Regards
--
David Mitchell
Software Engineer
Telogis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.mitchell@telogis.com
Subject: Re: How can I speed up this function?
In-Reply-To: <42C0B7EE.4040608@telogis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox