Hello Kevin
> If you use EXPLAIN with both statements...
Yes, the plans are indeed very different.
Here is the statement, set to update up to 100,000 records, which took
about 5 seconds to complete:
UPDATE
table_A
SET
field_1 = table_B.field_1
, field_2 = table_B.field_2
FROM
table_B
Harry Mantheakis wrote:
The mystery remains, for me: why updating 100,000 records could
complete in as quickly as 5 seconds, whereas an attempt to update a
million records was still running after 25 minutes before we killed it?
The way you were doing this originally, it was joining every recor
On Mon, Jun 27, 2011 at 5:37 PM, wrote:
>> The mystery remains, for me: why updating 100,000 records could complete
>> in as quickly as 5 seconds, whereas an attempt to update a million
>> records was still running after 25 minutes before we killed it?
>
> Hi, there's a lot of possible causes. Us
> The mystery remains, for me: why updating 100,000 records could complete
> in as quickly as 5 seconds, whereas an attempt to update a million
> records was still running after 25 minutes before we killed it?
Hi, there's a lot of possible causes. Usually this is caused by a plan
change - imagine
Harry Mantheakis wrote:
> I am glad to report that the 'salami-slice' approach worked nicely
> - all done in about 2.5 hours.
Glad to hear it!
> The mystery remains, for me: why updating 100,000 records could
> complete in as quickly as 5 seconds, whereas an attempt to update
> a million rec
I am glad to report that the 'salami-slice' approach worked nicely - all
done in about 2.5 hours.
Instead of using an all-in-one-go statement, we executed 800 statements,
each updating 100,000 records. On average it tool about 10-seconds for
each statement to return.
This is "thinking out of
I called VACUUM on the database after abandoning the original all-in-one
update.
And I have a daily cron script that executes the following statement:
sudo -u postgres /usr/bin/vacuumdb -U postgres --all --analyze
But I had not considered using CLUSTER - I will certainly look into that.
Thank
Harry Mantheakis wrote:
> I have stopped the all-in-one-go update from executing, and now I
> am executing a series of statements, each constrained to update no
> more than 100,000 records at a time.
>
> Interesting fact: updating 100,000 rows takes 5 seconds. Quick.
One last thing -- all the
> try a series of smaller examples...
Mark, that was the tip the saved me!
Many thanks.
Harry Mantheakis
London, UK
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Thank you so much for all the feedback, Kevin - much appreciated.
I have stopped the all-in-one-go update from executing, and now I am
executing a series of statements, each constrained to update no more
than 100,000 records at a time.
Interesting fact: updating 100,000 rows takes 5 seconds.
Harry Mantheakis wrote:
>> It will be a lot faster if you can drop all indices...
>
> This is counter-intuitive - because the WHERE clause is matching
> the only two indexed fields, and my understanding is that querying
> on indexed fields is faster than querying on fields that are not
> indexe
Harry Mantheakis wrote:
> IOW how do I convert - guesstimate! - these numbers into
> (plausible) time values?
They are abstract and it only matters that they are in the right
ratios to one another so that the planner can accurately pick the
cheapest plan. With the default settings, seq_page_c
Mark Thornton wrote:
> On 23/06/11 16:05, Harry Mantheakis wrote:
>> UPDATE
>> table_A
>> [ ... ]
>> FROM
>> table_B
>> WHERE
>> table_B.id = table_A.id
> I frequently get updates involving a FROM clause wrong --- the
> resulting table is correct but the running time is quadratic.
The most
On 23/06/11 16:05, Harry Mantheakis wrote:
Hello
I am attempting to run an update statement that copies two fields from
one table to another:
UPDATE
table_A
SET
(
field_1
, field_2
) = (
table_B.field_1
, table_B.field_2
)
FROM
table_B
WHERE
table_B.id = table_A.id
;
I frequently get
Thanks again Claudio.
It does not look like its locked/blocked - but your hint about doing
this task in smaller batches is a good one, and it would be easy enough
to organise.
I am going to let this task run over the week-end, and then decide.
Either way, I shall update this thread.
Much o
24.06.11 14:16, Harry Mantheakis написав(ла):
> EXPLAIN the statement
Here is the EXPLAIN result:
--
QUERY PLAN
--
Hash Join (cost=2589312.08..16596998.47 row
On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
wrote:
>
>> there are lots of ways in which it could be struggling...
>
> I have been monitoring the server with IOSTAT -d and IOSTAT -c and I cannot
> see anything alarming.
If iostat doesn't show disk load, either iostat doesn't work well
(which
Thank you Claudio.
> there are lots of ways in which it could be struggling...
I have been monitoring the server with IOSTAT -d and IOSTAT -c and I
cannot see anything alarming.
> It will be a lot faster if you can drop all indices...
This is counter-intuitive - because the WHERE clause is m
Thank you Kevin.
> SET field_1 = table_B.field_1, field_2 = table_B.field_2
I will try that, if I have to next time.
> add something like this toyour WHERE clause,
> to prevent unnecessary updates:
>
> AND (table_B.field_1 IS DISTINCT FROM table_A.field_1
>OR table_B.field_2 IS DISTINCT F
Harry Mantheakis wrote:
> UPDATE
>table_A
> SET
> (
>field_1
> , field_2
> ) = (
> table_B.field_1
> , table_B.field_2
> )
> FROM
> table_B
> WHERE
> table_B.id = table_A.id
> ;
I would have just done:
SET field_1 = table_B.field_1, field_2 = table_B.field_2
instead of using row v
On Thu, Jun 23, 2011 at 5:05 PM, Harry Mantheakis
wrote:
> TOP, FREE and VMSTAT utilities indicate that only about half of the 6GB of
> memory is being used, so I have no reason to believe that the server is
> struggling.
You have a hinky idea of server load.
Mind you, there are lots of ways in
Hello
I am attempting to run an update statement that copies two fields from
one table to another:
UPDATE
table_A
SET
(
field_1
, field_2
) = (
table_B.field_1
, table_B.field_2
)
FROM
table_B
WHERE
table_B.id = table_A.id
;
Table "table_B" contains almost 75 million records, with IDs t
22 matches
Mail list logo
