Please help - performance problems

Hello,
I have big performance issues with my (Java) application and Postgres
(7.4 on Linux).
I don't know if the problem is on my side (the application) or on the
database side, or both.
Here's the scoop:

I have a table, something like this:
CREATE TABLE my_table(
id bigint PRIMARY KEY,
name character varying(256),
status smallint
)
My application reads data from this table, one at the time, process it
and saves it back. Since this table holds a large number of rows,
performance is a big issue.
So I need a function which returns only one row from the table, as a
pair of (id, name) where status=1, ordered by name. Since my
application is multithreaded, I need to flag the selected row (with a
special status value) so other thread doesn't select the same row
while is being processed.

I have my own version of this function, it works, but on a table
currently with about 500.000 rows, it takes minutes to
complete...un-acceptable.

CREATE TYPE entry AS (id bigint, name character varying(256));
CREATE OR REPLACE FUNCTION getNextEntry() RETURNS entry AS '
DECLARE
my_record RECORD;
e entry%rowtype;
BEGIN
SELECT id, name into my_record
FROM my_table WHERE status=0
ORDER BY name LIMIT 1 FOR UPDATE;
e.id := my_record.id;
e.name := my_record.name;
update my_table set status=2 where id = my_record.id;
return e;
END;
' LANGUAGE plpgsql;

Please tell me what I do wrong, how I can do better or best show me
how it should be done.
I even tried different indexes but it didn't make any difference. I
feel that my function could be also improved a lot, from the
programming point of view.

As a side note: I've searched this newsgroup for similar performance
issues, I have found many complains but no concrete solutions. I have
seen people comparing Postgres to other databases from the performance
point of view, with results quite unexpected, where a SQLServer would
be more than 10 times faster than Postgres (at least for a number of
"normal" situations)...how could that be? Even Oracle is faster.
I like Postgres and I want to continue using it but my current project
is very sensitive from the performance perpective...please let me know
what can I do.
And another side note: I looked for a performance thread under
Postgres newsgroup but I couldn't find it.

many thanks!

Re: Please help - performance problems

I have news...good news for me:)
even though I wasn't able to find the answers I was looking for, I did
something that made a big difference: by removing the ORDER BY clause,
the same function takes now 5 milliseconds (instead of sometimes 10
minutes).
I have tried to vacuum, analyze, etc...nothing worked.
I post this hoping it could help somebody.


ctrl@altonsys.com (ctrl) wrote in message news:<46a31c4d.0407201842.48d29401@posting.google.com>...
> Hello,
> I have big performance issues with my (Java) application and Postgres
> (7.4 on Linux).
> I don't know if the problem is on my side (the application) or on the
> database side, or both.
> Here's the scoop:
>
> I have a table, something like this:
> CREATE TABLE my_table(
> id bigint PRIMARY KEY,
> name character varying(256),
> status smallint
> )
> My application reads data from this table, one at the time, process it
> and saves it back. Since this table holds a large number of rows,
> performance is a big issue.
> So I need a function which returns only one row from the table, as a
> pair of (id, name) where status=1, ordered by name. Since my
> application is multithreaded, I need to flag the selected row (with a
> special status value) so other thread doesn't select the same row
> while is being processed.
>
> I have my own version of this function, it works, but on a table
> currently with about 500.000 rows, it takes minutes to
> complete...un-acceptable.
>
> CREATE TYPE entry AS (id bigint, name character varying(256));
> CREATE OR REPLACE FUNCTION getNextEntry() RETURNS entry AS '
> DECLARE
> my_record RECORD;
> e entry%rowtype;
> BEGIN
> SELECT id, name into my_record
> FROM my_table WHERE status=0
> ORDER BY name LIMIT 1 FOR UPDATE;
> e.id := my_record.id;
> e.name := my_record.name;
> update my_table set status=2 where id = my_record.id;
> return e;
> END;
> ' LANGUAGE plpgsql;
>
> Please tell me what I do wrong, how I can do better or best show me
> how it should be done.
> I even tried different indexes but it didn't make any difference. I
> feel that my function could be also improved a lot, from the
> programming point of view.
>
> As a side note: I've searched this newsgroup for similar performance
> issues, I have found many complains but no concrete solutions. I have
> seen people comparing Postgres to other databases from the performance
> point of view, with results quite unexpected, where a SQLServer would
> be more than 10 times faster than Postgres (at least for a number of
> "normal" situations)...how could that be? Even Oracle is faster.
> I like Postgres and I want to continue using it but my current project
> is very sensitive from the performance perpective...please let me know
> what can I do.
> And another side note: I looked for a performance thread under
> Postgres newsgroup but I couldn't find it.
>
> many thanks!

Re: Please help - performance problems

ctrl wrote:

> I have news...good news for me:)
> even though I wasn't able to find the answers I was looking for, I did
> something that made a big difference: by removing the ORDER BY clause,
> the same function takes now 5 milliseconds (instead of sometimes 10
> minutes).
> I have tried to vacuum, analyze, etc...nothing worked.
> I post this hoping it could help somebody.

How many memory are you using for the sort operations, is that column
indexed ?



Regards
Gaetano Mendola