From: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> |
---|---|
To: | ginkgo36 <ginkgo56(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: :Posgres - performance problem |
Date: | 2015-03-25 11:50:05 |
Message-ID: | CAAJSdjgnGF2t9=D7qbvmRKQV8ozLwMhF5S0F0rBsiUkLdU1V2w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Mar 25, 2015 at 4:19 AM, ginkgo36 <ginkgo56(at)gmail(dot)com> 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.
>
> alter table master_items add "TYPE-DE" varchar default ''
> -- Query returned successfully with no result in 1211019 ms.
>
> update master_items set "feedback_to_de" = 'Yes'
> --Query returned successfully: 591268 rows affected, 1589335 ms execution
> time.
>
> Can you help me find any way to increase performance?
>
> Thanks all
I am not any kind of a performance expert. But the first thing that I
would try is an EXPLAIN. If you're using the psql line command, I'd do
something like:
BEGIN; -- BEGIN TRANSACTION
EXPLAIN (ANALYZE, TIMING) UPDATE master_items SET temp1 = "where temp1 <>";
ROLLBACK;
I'd put the EXPLAIN in a transaction that I roll back so that I
wouldn't actually update anything permanently . Also, doing a simple
ANALYZE on the table might help some. I'm not sure.
ANALYZE master_items;
--
If you sent twitter messages while exploring, are you on a textpedition?
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Mogensen | 2015-03-25 12:14:10 | Re: BDR - triggers on receiving node? |
Previous Message | Craig Ringer | 2015-03-25 11:32:01 | Re: BDR - triggers on receiving node? |