Postgres Pro
Downloads
Home   >   mailing lists

Re: very slow update query - Mailing list pgsql-novice

From Ilija Vidoevski
Subject Re: very slow update query
Date
Msg-id 1343672553.6168.YahooMailNeo@web113106.mail.gq1.yahoo.com
Whole thread Raw
In response to very slow update query  (Ilija Vidoevski <ilija.vidoevski@yahoo.com>)
List pgsql-novice
Tree view
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

>
>


pgsql-novice by date:

Previous
From: Ilija Vidoevski
Date:
Subject: very slow update query
Next
From: Tom Lane
Date:
Subject: Re: very slow update query