Re: simple update query too long

On Fri, May 13, 2011 at 2:07 AM, F T <oukile(at)gmail(dot)com> wrote:
> Thanks for your ideas.
>
> I have rerun my tests and I agree with Merlin, PostgreSQL is not adapted at
> all to handle wide updates.
>
> Summary :
> The table contains 2 millions rows.
>
> Test 1 :
> UPDATE grille SET inter=0; -> It tooks 10 hours
>
> Test 2 :
> I remove the spatial Gist index, and the constraints : I just keep the
> primary key.
> UPDATE grille SET inter=0; -> it tooks 6 hours.
>
> This is better but it is still not acceptable.
>
> And if I run CREATE TABLE test AS SELECT * FROM grille, it only takes 11
> seconds, incredible...

my experiences do not match yours:
postgres=# create table foo as select v as id, v as val, lpad('', 100,
'x') as various_data from generate_series(1,2000000) v;
SELECT 2000000
Time: 6985.000 ms
postgres=# create index on foo(id);
CREATE INDEX
Time: 7131.000 ms
postgres=# update foo set val = 0;
UPDATE 2000000
Time: 84524.000 ms
postgres=#

85 seconds is certainly a lot worse than 13, but nowhere near 6
hours...can we see a \d on the table as you have it with just the
primary key?

merlin