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

Re: How can I speed up this function?

From: David Mitchell <david(dot)mitchell(at)telogis(dot)com>
To: Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I speed up this function?
Date: 2005-06-28 04:55:00
Message-ID: 42C0D824.1030105@telogis.com (view raw or flat)
Thread:
Lists: pgsql-performance
The function I have exits the loop when the count hits 100 yes, but the 
inner loop can push the count up as high as necessary to select all the 
statements for a transaction, so by the time it exits, the count could 
be much higher. I do want to limit the statements, but I want to get 
enough for complete transactions.

David

Gnanavel Shanmugam wrote:
> But in the function you are exiting the loop when the count hits 100. If you
> do not want to limit the statements then remove the limit clause from the
> query I've written.
> 
> with regards,
> S.Gnanavel
> 
> 
> 
>>-----Original Message-----
>>From: david(dot)mitchell(at)telogis(dot)com
>>Sent: Tue, 28 Jun 2005 16:29:32 +1200
>>To: s(dot)gnanavel(at)inbox(dot)com
>>Subject: Re: [PERFORM] How can I speed up this function?
>>
>>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(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
>>
>>
>>--
>>David Mitchell
>>Software Engineer
>>Telogis


-- 
David Mitchell
Software Engineer
Telogis

In response to

Responses

pgsql-performance by date

Next:From: Michael FuhrDate: 2005-06-28 05:16:26
Subject: Re: LIKE search with ending % not optimized in v8
Previous:From: Luke LonerganDate: 2005-06-28 04:44:59
Subject: Re: COPY FROM performance improvements

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