Skip site navigation (1) Skip section navigation (2)

Re: How can I speed up this function?

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: David Mitchell <david(dot)mitchell(at)telogis(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I speed up this function?
Date: 2005-06-28 03:04:48
Message-ID: 42C0BE50.3090105@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
What's wrong with Slony?

David Mitchell wrote:
> 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
> 


In response to

Responses

pgsql-performance by date

Next:From: David MitchellDate: 2005-06-28 03:11:02
Subject: Re: How can I speed up this function?
Previous:From: Karl O. PincDate: 2005-06-28 03:03:06
Subject: Re: Performance analysis of plpgsql code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group