From: David Mitchell <david.mitchell@telogis.com>
To: Gnanavel Shanmugam <s.gnanavel@inbox.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How can I speed up this function?
Date: Tue, 28 Jun 2005 16:29:32 +1200
Message-ID: <42C0D22C.6060609@telogis.com> (raw)
In-Reply-To: <96ACC448E50.000007FCs.gnanavel@inbox.com>
References: <96ACC448E50.000007FCs.gnanavel@inbox.com>
Hi Gnanavel,
Thanks, but that will only return at most 100 statements. If there is a
transaction with 110 statements then this will not return all the
statements for that transaction. We need to make sure that the function
returns all the statements for a transaction.
Cheers
David
Gnanavel Shanmugam wrote:
> 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.mitchell@telogis.com
>>Sent: Tue, 28 Jun 2005 14:37:34 +1200
>>To: pgsql-performance@postgresql.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
--
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, s.gnanavel@inbox.com
Subject: Re: How can I speed up this function?
In-Reply-To: <42C0D22C.6060609@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