Обсуждение: PG 7.1.3, copy from file suddenly goes very slow

Hi

I have been using Postgresql for about 3 weeks now with no problem.

The main task I use PG for is to import data from a file to a
temporary
table, which triggers a function that inserts (unique) data to the
"main-table" (called metar)

Today suddenly everything went *very* slow. Restart of
server/postgresql and
running vacuum did not help. I have now approx. 800 000 rows in the
"main-table", disk usagem approx. 1GB.

The function triggered when inserting data to temporary table looks
like:

CREATE FUNCTION test_inserted_data() RETURNS OPAQUE AS '
DECLARE
curr_val TEXT;
BEGIN

SELECT UNIK_ID INTO curr_val FROM metar WHERE unik_id = new.UNIK_ID;
IF NOT FOUND THEN
  insert into metar(
...... (text excluded)
ELSE
   update metar set
....... (text excluded)

WHERE UNIK_ID = new.UNIK_ID;
END IF;
RETURN old;
END;

...where unik_id is an index

Have I somehow ran my head into a limitation in my current setup of
Postgresql? My postgresql.conf is "empty", that is: everything is
commented out as default.

Hope someone has ideas about this!

best regards
Svein Are
Norway

Have you done a VACUUM ANALYZE; on "metar"?  For your
setup, you will need to do that regularly to maintain
performance.

--- Svein Are <svein.skarstein@weather.no> wrote:
> Hi
>
> I have been using Postgresql for about 3 weeks now
> with no problem.
>
> The main task I use PG for is to import data from a
> file to a
> temporary
> table, which triggers a function that inserts
> (unique) data to the
> "main-table" (called metar)
>
> Today suddenly everything went *very* slow. Restart
> of
> server/postgresql and
> running vacuum did not help. I have now approx. 800
> 000 rows in the
> "main-table", disk usagem approx. 1GB.
>
> The function triggered when inserting data to
> temporary table looks
> like:
>
> CREATE FUNCTION test_inserted_data() RETURNS OPAQUE
> AS '
> DECLARE
> curr_val TEXT;
> BEGIN
>
> SELECT UNIK_ID INTO curr_val FROM metar WHERE
> unik_id = new.UNIK_ID;
> IF NOT FOUND THEN
>   insert into metar(
> ...... (text excluded)
> ELSE
>    update metar set
> ....... (text excluded)
>
> WHERE UNIK_ID = new.UNIK_ID;
> END IF;
> RETURN old;
> END;
>
> ...where unik_id is an index
>
> Have I somehow ran my head into a limitation in my
> current setup of
> Postgresql? My postgresql.conf is "empty", that is:
> everything is
> commented out as default.
>
> Hope someone has ideas about this!
>
> best regards
> Svein Are
> Norway
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/