Re: [PERFORM] Postgresql update op is very very slow

On Jun 24, 2008, at 9:12 PM, jay wrote: I've a table with about 34601755 rows ,when I execute 'update msg_table set type=0;' is very very slow, cost several hours, but still not complete? Why postgresql is so slowly? Is the PG MVCC problem? But I try it on Mysql, the same table and rows

Re: [PERFORM][SOLVED] Postgresql is very slow

Thank you all very very much. After running CLUSTER on the "USERS" table, now the speed is very very good. Now i have also understood the importance of VACUUM and ANALYZE. Once again thank you all very very much. You guys rock. --- On Tue, 24/6/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

[PERFORM] Postgresql update op is very very slow

I've a table with about 34601755 rows ,when I execute 'update msg_table set type=0;' is very very slow, cost several hours, but still not complete? Why postgresql is so slowly? Is the PG MVCC problem? But I try it on Mysql, the same table and rows, it only cost about 340 seconds. Any idea for

Re: [PERFORM] Postgresql is very slow

SELECT relpages, reltuples FROM pg_class WHERE relname ='users'; relpages | reltuples --+--- 54063 | 2307 (1 row) This is a horribly bloated table. The Output of query on the old server which is fast relpages | reltuples --+--- 42 |

Re: [PERFORM] Postgresql is very slow

Thank you and all very much for your support. Now i have understood the problem related to my server. I will try the suggested thing like CLUSTER and then let you all know what happens after that. Once again Thanking you all. Bijayant Kumar --- On Tue, 24/6/08, [EMAIL PROTECTED] <[EMAIL PROTE

Re: [PERFORM] Postgresql is very slow

>> Not necessarily, the 'cost' depends on >> seq_page_cost and there might be >> other value than 1 (which is the default). A better >> approach is >> >> SELECT relpages, reltuples FROM pg_class WHERE relname = >> 'users'; >> >> which reads the values from system catalogue. >> > The Output of query

Re: [PERFORM] Postgresql is very slow

2008/6/24 Scott Marlowe <[EMAIL PROTECTED]>: > On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <[EMAIL PROTECTED]> wrote: (...) >> The output of EXPLAIN query; >> >> select * from USERS where email like '%bijayant.kumar%'; >> This simplest query tooks 10 minutes and server loads goes from 0.35 to

Re: [PERFORM] Postgresql is very slow

--- On Tue, 24/6/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > From: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Subject: Re: [PERFORM] Postgresql is very slow > To: "Scott Marlowe" <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], pgsql-performance@postgresql.org > Date: Tuesday, 2

Re: [PERFORM] Postgresql is very slow

>> I was not aware of the VACUUM functionality earlier, but some times back >> i read and run this on the server but i did not achieve anything in >> terms of performance. The server is running from 1 to 1.5 years and we >> have done VACUUM only once. > > vacuuming isn't so much about performance a

Re: [PERFORM] Postgresql is very slow

> Definitely need a vacuum full on this table, likely followed by a reindex. Or a cluster on the table... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Postgresql is very slow

On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <[EMAIL PROTECTED]> wrote: OK, you don't have a ton of updates each day, but they add up over time. > I was not aware of the VACUUM functionality earlier, but some times back i > read and run this on the server but i did not achieve anything in te