Re: Crashing DB or Server? - Mailing list pgsql-performance

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");