Re: very slow update query

Tom,

This is table DDL

CREATE TABLE public.finarh (
  godina CHAR(4) NOT NULL,
  re CHAR(2) NOT NULL,
  konto CHAR(9) NOT NULL,
  nalog CHAR(6) NOT NULL,
  datanal TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  vid CHAR(1) NOT NULL,
  dokument CHAR(12),
  datadok TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  valudok TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  dp CHAR(1) NOT NULL,
  iznos NUMERIC DEFAULT 0 NOT NULL,
  diznos NUMERIC DEFAULT 0,
  piznos NUMERIC DEFAULT 0,
  slog INTEGER DEFAULT 0 NOT NULL,
  pole CHAR(1) NOT NULL,
  ddv CHAR(3),
  vidddv CHAR(3),
  danos1 NUMERIC DEFAULT 0,
  tarbr1 CHAR(4),
  danok1 NUMERIC DEFAULT 0,
  danos2 NUMERIC DEFAULT 0,
  tarbr2 CHAR(4),
  danok2 NUMERIC DEFAULT 0,
  oe CHAR(4) NOT NULL,
  korisnik CHAR(15),
  userid CHAR(10),
  denari CHAR(2),
  deviza CHAR(3),
  kurs NUMERIC DEFAULT 0,
  diznosd NUMERIC DEFAULT 0,
  piznosd NUMERIC DEFAULT 0,
  opis CHAR(1),
  CONSTRAINT finarh_pkey PRIMARY KEY(godina, re, nalog, slog),
  CONSTRAINT finarh_fk FOREIGN KEY (konto)
    REFERENCES public.konta(konto)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT finarh_fk2 FOREIGN KEY (re)
    REFERENCES public.firmi(konto)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT finarh_fk3 FOREIGN KEY (oe)
    REFERENCES public.patnici(konto)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT finarh_fk4 FOREIGN KEY (deviza)
    REFERENCES public.devizi(deviza)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX _dta_index_finarh_7_149575571__k1_k2_k5_k3_k24_k4_k7_k12_k13_k8 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", datanal, konto COLLATE pg_catalog."default", oe COLLATE pg_catalog."default", nalog COLLATE pg_catalog."default", dokument COLLATE pg_catalog."default", diznos, piznos, datadok, valudok, vid COLLATE pg_catalog."default", slog, kurs);

CREATE INDEX finarh_01 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", datanal, konto COLLATE pg_catalog."default", oe COLLATE pg_catalog."default", nalog COLLATE pg_catalog."default", dokument COLLATE pg_catalog."default", diznos, piznos, datadok, valudok, slog, kurs);

CREATE INDEX finarh_02 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", datanal, konto COLLATE pg_catalog."default", oe COLLATE pg_catalog."default", vid COLLATE pg_catalog."default");

CREATE INDEX finarh_03 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", datanal, konto COLLATE pg_catalog."default", oe COLLATE pg_catalog."default", dokument COLLATE pg_catalog."default", diznos, piznos, datadok, valudok, dp COLLATE pg_catalog."default", nalog COLLATE pg_catalog."default");

CREATE INDEX finarh_04 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", datanal, nalog COLLATE pg_catalog."default", vid COLLATE pg_catalog."default");

CREATE INDEX pg_finarh_01 ON public.finarh
  USING btree (godina COLLATE pg_catalog."default", re COLLATE pg_catalog."default", konto COLLATE pg_catalog."default", oe COLLATE pg_catalog."default", datanal);

ALTER TABLE public.finarh
  CLUSTER ON pg_finarh_01;

Changes in default config file made EnterpriseDB Tuning Wizard.

In my accounting app many times is necessary to insert thousands of records and also to update thousands of records . If inserting is also very slow,  I am  not sure that Postgresql is wright choice.

Regards,
Ilija

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Sent: Monday, July 30, 2012 9:10 PM
Subject: Re: [NOVICE] very slow update query

Ilija Vidoevski <ilija(dot)vidoevski(at)yahoo(dot)com> writes:
> Explain query plan is:

> "Update on finarh  (cost=0.00..12049.99 rows=177714 width=172)"
> "  ->  Seq Scan on finarh  (cost=0.00..12049.99 rows=177714 width=172)"

> Why execution time is so loooong ?

EXPLAIN ANALYZE output might be more informative.  One thing it would
tell us is if the time is going into foreign key checks, for instance.
You've provided no information whatever about the table's schema, so
it's impossible to guess if the time is going into the actual updates,
or index updates, or constraint checks, or TOAST overhead, or what.

There's some info here about the type of information that's useful
when trying to debug a performance problem:
http://wiki.postgresql.org/wiki/Slow_Query_Questions

            regards, tom lane