Re: Crashing DB or Server?

Hi,

actually every SELECT statements takes a couple of minutes.
For example
SELECT * FROM pg_stat_activity already takes 260 sec.

And the IOWAIT value increases just after starting the postmaster, no
querys are processed.

I started vacuumizing the tables of the DB. Still, it doesn't make a
difference.

So I don't know if the structure of the tables are relevant.
For example, I have got about 30 of those:

CREATE TABLE "public"."tbl_highscore_app4" (
"id" BIGSERIAL,
"userid" INTEGER NOT NULL,
"score" INTEGER DEFAULT 0 NOT NULL,
"occured" DATE DEFAULT now() NOT NULL,
CONSTRAINT "tbl_highscore_app4_pkey" PRIMARY KEY("userid")
) WITHOUT OIDS;

the select-statements are done through functions, for example

CREATE OR REPLACE FUNCTION "public"."getownrankingapp4" (integer, integer)
RETURNS integer AS'
DECLARE i_userid INTEGER;
DECLARE i_score INTEGER;
DECLARE i_rank INTEGER;
begin
i_userid := $1;
i_score := $2;
i_rank := 1;
if i_score <= 0 then
SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp;
if i_rank IS null then
i_rank = 1;
else
i_rank = i_rank +1;
end if;
else
SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp WHERE
score>=i_score; if i_rank IS null then i_rank = 1; end if; end if;
return (i_rank);
END
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

The tmp table looks like this (and is filled once a night with the current
data):

CREATE TABLE "public"."tbl_highscore_app4_tmp" (
"id" INTEGER NOT NULL,
"userid" INTEGER NOT NULL,
"score" INTEGER NOT NULL
) WITH OIDS;

CREATE INDEX "tbl_highscore_app4_tmp_index" ON
"public"."tbl_highscore_app4_tmp"
USING btree ("score");