Re: How can I speed up this function?
От | Gnanavel Shanmugam |
---|---|
Тема | Re: How can I speed up this function? |
Дата | |
Msg-id | 9761F49E8B0.0000087Cs.gnanavel@inbox.com обсуждение исходный текст |
Ответ на | How can I speed up this function? (David Mitchell <david.mitchell@telogis.com>) |
Список | pgsql-performance |
I think the following logic will do want you expect FOR statement IN <previous_query> LOOP -- update statement goes here -- if count > 100 and temp <> transaction_id then // reaches here only if the transaction is complete return; else count:= count+1; temp:=transaction_id; end if; end loop; with regards, S.Gnanavel > -----Original Message----- > From: david.mitchell@telogis.com > Sent: Tue, 28 Jun 2005 16:55:00 +1200 > To: s.gnanavel@inbox.com > Subject: Re: [PERFORM] How can I speed up this function? > > 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.mitchell@telogis.com > >>Sent: Tue, 28 Jun 2005 16:29:32 +1200 > >>To: s.gnanavel@inbox.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.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 > > > -- > David Mitchell > Software Engineer > Telogis
В списке pgsql-performance по дате отправления: