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

Re: How can I speed up this function?

From: Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com>
To: David Mitchell <david(dot)mitchell(at)telogis(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I speed up this function?
Date: 2005-06-28 04:06:11
Message-ID: 96ACC448E50.000007FCs.gnanavel@inbox.com (view raw or flat)
Thread:
Lists: pgsql-performance
Merge the two select statements like this and try,

SELECT t.trans_id as ID,s.id, s.transaction_id, s.table_name, s.op, s.data
   FROM pending_trans AS t join dbmirror.pending_statement AS s
   on (s.transaction_id=t.id)
WHERE t.fetched = false order by t.trans_id,s.id limit 100;

 If the above query works in the way you want, then you can also do the
update
using the same.

with regards,
S.Gnanavel


> -----Original Message-----
> From: david(dot)mitchell(at)telogis(dot)com
> Sent: Tue, 28 Jun 2005 14:37:34 +1200
> To: pgsql-performance(at)postgresql(dot)org
> Subject: [PERFORM] How can I speed up this function?
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

In response to

Responses

pgsql-performance by date

Next:From: David MitchellDate: 2005-06-28 04:29:32
Subject: Re: How can I speed up this function?
Previous:From: David MitchellDate: 2005-06-28 03:33:44
Subject: Re: How can I speed up this function?

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