How can I speed up this function?
От | David Mitchell |
---|---|
Тема | How can I speed up this function? |
Дата | |
Msg-id | 42C0B7EE.4040608@telogis.com обсуждение исходный текст |
Ответы |
Re: How can I speed up this function?
(Christopher Kings-Lynne <chriskl@familyhealth.com.au>)
Re: How can I speed up this function? (Keith Worthington <KeithW@NarrowPathInc.com>) Re: How can I speed up this function? (Gnanavel Shanmugam <s.gnanavel@inbox.com>) Re: How can I speed up this function? (Klint Gore <kg@kgb.une.edu.au>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления:
Предыдущее
От: "Karl O. Pinc"Дата:
Сообщение: Re: Poor index choice -- multiple indexes of the same