I'm not an expert either, but your data model sounds very broken as well... I guess it's possible that each query would need all 417 columns but it seems unlikely...
If that were normalized into 'n' tables then each query would be returning a whole lot less data...
I've never heard of a database being stuffed into one table before...
What is your use case / requirement for one table?
Gary
On Wed, Mar 25, 2015 at 4:50 AM, John McKown <john.archie.mckown@xxxxxxxxx> wrote:
On Wed, Mar 25, 2015 at 4:19 AM, ginkgo36 <ginkgo56@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
