Re: [HACKERS] Transactions vs speed.
mlw <[EMAIL PROTECTED]> writes: > Take this update: > update table set field = 'X' ; > This is a very expensive function when the table has millions of rows, > it takes over an hour. If I dump the database, and process the data with > perl, then reload the data, it takes minutes. Most of the time is used > creating indexes. Hm. CREATE INDEX is well known to be faster than incremental building/ updating of indexes, but I didn't think it was *that* much faster. Exactly what indexes do you have on this table? Exactly how many minutes is "minutes", anyway? You might consider some hack like drop inessential indexes; UPDATE; recreate dropped indexes; "inessential" being any index that's not UNIQUE (or even the UNIQUE ones, if you don't mind finding out about uniqueness violations at the end). Might be a good idea to do a VACUUM before rebuilding the indexes, too. It won't save time in this process, but it'll be cheaper to do it then rather than later. regards, tom lane PS: I doubt transactions have anything to do with it.
Re: [HACKERS] Transactions vs speed.
* mlw <[EMAIL PROTECTED]> [010113 17:19] wrote: > I have a question about Postgres: > > Take this update: > update table set field = 'X' ; > > > This is a very expensive function when the table has millions of rows, > it takes over an hour. If I dump the database, and process the data with > perl, then reload the data, it takes minutes. Most of the time is used > creating indexes. > > I am not asking for a feature, I am just musing. Well you really haven't said if you've tuned your database at all, the way postgresql ships by default it doesn't use a very large shared memory segment, also all the writing (at least in 7.0.x) is done syncronously. There's a boatload of email out there that explains various ways to tune the system. Here's some of the flags that I use: -B 32768 # uses over 300megs of shared memory -o "-F" # tells database not to call fsync on each update -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
