Обсуждение: very slow update query

Поиск

Список

Период

Сортировка

very slow update query

От

Ilija Vidoevski

Дата:

30 июля 2012 г., 17:51:12

I need to update one table in my database with simple code

This is the script

update finarh
set vid = left(nalog,1)

Table has 177714 rows.

First execution time was : 00:02:39 minutes

Repeated execution time: 00:01:03 minutes.

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 ?

Thank's

Ilija Vidoevski

Re: very slow update query

От

Ilija Vidoevski

Дата:

30 июля 2012 г., 18:23:54

Andreas,

I am migrating from SQL Server 2008 R2 express to Postgres.

This code is for fixing mistake made during transferring data from SQL Server tables.

I try this query on SQL Server 2008 database and I got this results

First execution time : 06:61 sec
Repeated execution time: 04:76 sec

Same table, same indexes.

It is huge difference.

I believe that is not something wrong in Postgresql engine.

Ilija Vidoevski

From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>; "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Monday, July 30, 2012 8:04 PM
Subject: Re: [NOVICE] very slow update query

Ilija Vidoevski <ilija.vidoevski@yahoo.com> hat am 30. Juli 2012 um 19:50
geschrieben:

> I need to update one table in my database with simple code
> This is the script
>
> update finarh
> set vid = left(nalog,1)
>
> Table has 177714 rows.
>
> First execution time was : 00:02:39 minutes
> Repeated execution time: 00:01:03 minutes.
>
> 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 ?
>
>

The database rewrite the whole table, 177 thousand records, this takes some
time...

Why you are doing that? the column vid are redundant, you should better use
select left(nalog,1) as vid and drop that vid-column from the table.

Regards, Andreas

>
>

Re: very slow update query

От

Tom Lane

Дата:

30 июля 2012 г., 19:10:52

Ilija Vidoevski <ilija.vidoevski@yahoo.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

Re: very slow update query

От

Andreas Kretschmer

Дата:

30 июля 2012 г., 19:24:08




Ilija Vidoevski <ilija.vidoevski@yahoo.com> hat am 30. Juli 2012 um 19:50
geschrieben:


> I need to update one table in my database with simple code
>  This is the script
>
>   update finarh
>   set vid = left(nalog,1)
>
>  Table has 177714 rows.
>
>  First execution time was : 00:02:39 minutes
>  Repeated execution time: 00:01:03 minutes.
>
>  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 ?
>
>



The database rewrite the whole table, 177 thousand records, this takes some
time...

Why you are doing that? the column vid are redundant, you should better use
select left(nalog,1) as vid and drop that vid-column from the table.

Regards, Andreas

>
>

Re: very slow update query

От

Ilija Vidoevski

Дата:

30 июля 2012 г., 20:17:22

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@sss.pgh.pa.us>
To: Ilija Vidoevski <ilija.vidoevski@yahoo.com>
Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Sent: Monday, July 30, 2012 9:10 PM
Subject: Re: [NOVICE] very slow update query

Ilija Vidoevski <ilija.vidoevski@yahoo.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

Re: very slow update query

От

Tom Lane

Дата:

31 июля 2012 г., 00:59:37

Ilija Vidoevski <ilija.vidoevski@yahoo.com> writes:
> This is table DDL

What I asked for was EXPLAIN ANALYZE output ... but anyway, the thing
that jumps out at me about that DDL is the six extremely wide indexes
(not even counting the primary key index).  It seems very unlikely
that those are going to repay their update maintenance costs.  I'd
try dropping all but the primary key and seeing if that improves the
update-speed situation at all.  If it does, I'd suggest reading
something about Postgres-oriented index design before you add anything
back.  There's material in the manual here:
http://www.postgresql.org/docs/9.1/static/indexes.html
but the key points I think you are missing is that indexes with more
than a few columns are seldom worth the trouble, and indexes with
identical leading columns are even more seldom worth the trouble.

            regards, tom lane

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: very slow update query

very slow update query

Re: very slow update query

Re: very slow update query

Re: very slow update query

Re: very slow update query

Re: very slow update query