Re: :Posgres - performance problem

On 3/25/2015 2:19 AM, ginkgo36 wrote:
> Hi all,
> I have 1 table have:
> - 417 columns
> - 600.000 rows data
> - 34 indexs
>
> when i use query on this table, it so long. ex:
>
> update master_items set
> temp1 = '' where temp1 <> '' --Query returned successfully: 435214 rows
> affected, 1016137 ms execution time.

that query is modifying 435000 rows of your table, and if temp1 is an
indexed field, the index has to be updated 435000 times, too.

note that in postgres, a 'update' translates into a INSERT and a DELETE

> alter table master_items add "TYPE-DE" varchar default ''
> -- Query returned successfully with no result in 1211019 ms.

that is rewriting all 600000 rows, to add this new field with its
default empty string content

> update master_items set "feedback_to_de" = 'Yes'
> --Query returned successfully: 591268 rows affected, 1589335 ms execution
> time.

that is modifying 591000 rows, essentially rewriting the whole table.

> Can you help me find any way to increase performance?

more/faster storage. faster CPU. more RAM.

or, completely rethink how you store this data and normalize it as
everyone else has said.

--
john, recycling bits in santa cruz