Re: :Posgres - performance problem

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(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>