Обсуждение: 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
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
>
Christopher Kings-Lynne wrote: > What's wrong with Slony? Because it's not multi-master. Our mirroring package is. -- David Mitchell Software Engineer Telogis
>> What's wrong with Slony? > > Because it's not multi-master. Our mirroring package is. I'm curious - how did you write a multi-master replication package in pgsql, when pgsql doesn't have 2 phase commits or any kind of distributed syncing or conflict resolution in a release version? Chris
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;
David,
I'm still a newbie and it may not affect performance but why are you
aliasing the tables? Can you not simply use
FOR transaction IN SELECT trans_id
FROM pending_trans
WHERE fetched = false
ORDER BY trans_id
LIMIT 50
and
FOR statement IN SELECT id,
transaction_id,
table_name,
op,
data
FROM dbmirror.pending_statement
WHERE pending_statement.transaction_id =
transaction.trans_id
ORDER BY pending_statement.id
I am pretty sure that the ORDER BY is slowing down both of these
queries. Since you are going to go through the whole table eventually
do you really need to sort the data at this point?
--
Kind Regards,
Keith
Christopher Kings-Lynne wrote: > > I'm curious - how did you write a multi-master replication package in > pgsql, when pgsql doesn't have 2 phase commits or any kind of > distributed syncing or conflict resolution in a release version? We didn't write it entirely in pgsql, there is a worker process that takes care of actually committing to the database. Cheers -- David Mitchell Software Engineer Telogis
Hi Keith, Unfortunately, we must have those sorts. The statements within a transaction must be executed on the slave in the same order as they were on the master, and similarly, transactions must also go in the same order. As for aliasing the tables, that is just a remnant from previous versions of the code. Thanks David Keith Worthington wrote: > I'm still a newbie and it may not affect performance but why are you > aliasing the tables? Can you not simply use > > FOR transaction IN SELECT trans_id > FROM pending_trans > WHERE fetched = false > ORDER BY trans_id > LIMIT 50 > > and > > FOR statement IN SELECT id, > transaction_id, > table_name, > op, > data > FROM dbmirror.pending_statement > WHERE pending_statement.transaction_id = > transaction.trans_id > ORDER BY pending_statement.id > > I am pretty sure that the ORDER BY is slowing down both of these > queries. Since you are going to go through the whole table eventually > do you really need to sort the data at this point? > -- David Mitchell Software Engineer Telogis
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
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
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
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
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
On Tue, 28 Jun 2005 14:37:34 +1200, David Mitchell <david.mitchell@telogis.com> wrote: > FOR transaction IN SELECT t.trans_id as ID > FROM pending_trans AS t WHERE fetched = false > ORDER BY trans_id LIMIT 50 What the the average number of statements per transaction? if avg > 2 then you could save a small amount of time by lowering the limit. You might also save some time by using FOR UPDATE on the select since the next thing you're going to do is update the value. > 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 Have you explained this to make sure it's using the created index? You might need to order by both transaction_id, id. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+