pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLong Running Update
16+ messages / 5 participants
[nested] [flat]
* Long Running Update
@ 2011-06-23 15:05 Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-23 19:32 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 12:52 ` Re: Long Running Update Mark Thornton <mthornton@optrak.com>
0 siblings, 3 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-23 15:05 UTC (permalink / raw)
To: pgsql-performance
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 that match
those in "table_A".
Both "field_1" and "field_2" are DOUBLE PRECISION. The ID fields are
SERIAL primary-key integers in both tables.
I tested (the logic of) this statement with a very small sample, and it
worked correctly.
The database runs on a dedicated Debian server in our office.
I called both VACUUM and ANALYZE on the databased before invoking this
statement.
The statement has been running for 18+ hours so far.
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.
My question is: can I reasonably expect a statement like this to
complete with such a large data-set, even if it takes several days?
We do not mind waiting, but obviously we do not want to wait unnecessarily.
Many thanks.
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-23 17:18 ` Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2 siblings, 1 reply; 16+ messages in thread
From: Claudio Freire @ 2011-06-23 17:18 UTC (permalink / raw)
To: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
On Thu, Jun 23, 2011 at 5:05 PM, Harry Mantheakis
<harry.mantheakis@riskcontrollimited.com> 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 which it could be struggling,
other than memory usage.
Like IO, CPU, lock contention...
In my experience, such huge updates struggle a lot with fsync and
random I/O when updating the indices.
It will be a lot faster if you can drop all indices (including the
PK), if you can.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
@ 2011-06-24 11:19 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 11:39 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 15:12 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 11:19 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
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 matching the
only two indexed fields, and my understanding is that querying on
indexed fields is faster than querying on fields that are not indexed.
(Note also, that the indexed field is NOT being updated.)
But if this update fails, I shall try what you suggest!
Kind regards
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 11:39 ` Claudio Freire <klaussfreire@gmail.com>
2011-06-24 12:39 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
1 sibling, 1 reply; 16+ messages in thread
From: Claudio Freire @ 2011-06-24 11:39 UTC (permalink / raw)
To: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
<harry.mantheakis@riskcontrollimited.com> 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 could be the case, I've found a few broken installations here
and there), or, perhaps, your update is waiting on some other update.
I've seen cases when there are application-level deadlocks (ie,
deadlocks, but that the database alone cannot detect, and then your
queries stall like that. It happens quite frequently if you try such a
massive update on a loaded production server. In those cases, the
technique someone mentioned (updating in smaller batches) usually
works nicely.
You should be able to see if it's locked waiting for something with
"select * from pg_stat_activity".
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 11:39 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
@ 2011-06-24 12:39 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
0 siblings, 0 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 12:39 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
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 obliged!
Harry Mantheakis
London, UK
On 24/06/2011 12:39, Claudio Freire wrote:
> On Fri, Jun 24, 2011 at 1:19 PM, Harry Mantheakis
> <harry.mantheakis@riskcontrollimited.com> 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 could be the case, I've found a few broken installations here
> and there), or, perhaps, your update is waiting on some other update.
>
> I've seen cases when there are application-level deadlocks (ie,
> deadlocks, but that the database alone cannot detect, and then your
> queries stall like that. It happens quite frequently if you try such a
> massive update on a loaded production server. In those cases, the
> technique someone mentioned (updating in smaller batches) usually
> works nicely.
>
> You should be able to see if it's locked waiting for something with
> "select * from pg_stat_activity".
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 15:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:29 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
1 sibling, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2011-06-24 15:12 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
Harry Mantheakis <harry.mantheakis@riskcontrollimited.com> 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
> indexed.
Because your UPDATE requires reading every tuple in every page of
both tables, it would be counter-productive to use the indexes.
Random access is much slower than sequential, so it's fastest to
just blast through both tables sequentially. The plan you showed
has it scanning through table_B and loading the needed data into RAM
in a hash table, then scanning through table_A and updating each row
based on what is in the RAM hash table.
For each row updated, if it isn't a HOT update, a new entry must be
inserted into every index on table_A, so dropping the indexes before
the update and re-creating them afterward would probably be a very
good idea if you're going to do the whole table in one go, and
possibly even if you're working in smaller chunks.
One thing which might help run time a lot, especially since you
mentioned having a lot of unused RAM, is to run the update with a
very hight work_mem setting in the session running the UPDATE.
> (Note also, that the indexed field is NOT being updated.)
That's one of the conditions for a HOT update. The other is that
there is space available on the same page for a new version of the
row, in addition to the old version.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 15:12 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-06-24 16:29 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 16:41 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 16:29 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Claudio Freire <klaussfreire@gmail.com>; 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. Quick.
I tried updating 1 million rows in one go, and the statement was still
running after 25 minutes, before I killed it!
So I am now executing an SQL script with almost 800 separate
update-statements, each set to update 100K records, and the thing is
trundling along nicely. (I am fortunate enough to be surrounded by
MatLab users, one of whom generated the 800-statement file in one minute
flat!)
Many thanks again for all the info.
Kind regards
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 15:12 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:29 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 16:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:49 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2011-06-24 16:41 UTC (permalink / raw)
To: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
Harry Mantheakis <harry.mantheakis@riskcontrollimited.com> 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 these updates, included the aborted attempt at
a single-pass update, may cause significant bloat in both the heap
and the index(es). I usually finish up with a CLUSTER on the table,
followed by a VACUUM ANALYZE on the table.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Re: Long Running Update Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 15:12 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:29 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 16:41 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-06-24 16:49 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
0 siblings, 0 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 16:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
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.
Thanks again.
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-23 19:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 11:16 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2011-06-23 19:32 UTC (permalink / raw)
To: pgsql-performance; Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
Harry Mantheakis <harry.mantheakis@riskcontrollimited.com> 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 value constructors. That might be slowing
things down a bit.
> I tested (the logic of) this statement with a very small sample,
> and it worked correctly.
Always a good sign. :-)
> The statement has been running for 18+ hours so far.
> My question is: can I reasonably expect a statement like this to
> complete with such a large data-set, even if it takes several
> days?
If it's not leaking memory, I expect that it will complete.
To get some sense of what it's doing, you could log on to another
connection and EXPLAIN the statement. (NOTE: Be careful *not* to
use EXPLAIN ANALYZE.)
Another thing to consider if you run something like this again is
that an UPDATE is an awful lot like an INSERT combined with a
DELETE. The way PostgreSQL MVCC works, the old version of each row
must remain until the updating transaction completes. If you were
to divide this update into a series of updates by key range, the new
versions of the rows from later updates could re-use the space
previously occupied by the old version of rows from earlier updates.
For similar reasons, you might want to add something like this to
your 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 FROM table_A.field_2);
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 19:32 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-06-24 11:16 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 11:45 ` Re: Long Running Update Vitalii Tymchyshyn <tivv00@gmail.com>
2011-06-24 14:00 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 11:16 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance
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 FROM table_A.field_2);
Thank you for that explanation - I will keep that in mind in future. (In
this case, the two fields that are being updated are all known to be
empty - hence, distinct - in the target table.)
> EXPLAIN the statement
Here is the EXPLAIN result:
----------------------------------------------------------------------
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=2589312.08..16596998.47 rows=74558048 width=63)
Hash Cond: (table_A.id = table_B.id)
-> Seq Scan on table_A(cost=0.00..1941825.05 rows=95612705 width=47)
-> Hash (cost=1220472.48..1220472.48 rows=74558048 width=20)
-> Seq Scan on table_B(cost=0.00..1220472.48 rows=74558048 width=20)
----------------------------------------------------------------------
The documentation says the 'cost' numbers are 'units of disk page fetches'.
Do you, by any chance, have any notion of how many disk page fetches can
be processed per second in practice - at least a rough idea?
IOW how do I convert - guesstimate! - these numbers into (plausible)
time values?
Kind regards
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 19:32 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 11:16 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 11:45 ` Vitalii Tymchyshyn <tivv00@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Vitalii Tymchyshyn @ 2011-06-24 11:45 UTC (permalink / raw)
To: pgsql-performance
24.06.11 14:16, Harry Mantheakis написав(ла):
>
> > EXPLAIN the statement
>
> Here is the EXPLAIN result:
>
> ----------------------------------------------------------------------
> QUERY PLAN
> ----------------------------------------------------------------------
> Hash Join (cost=2589312.08..16596998.47 rows=74558048 width=63)
> Hash Cond: (table_A.id = table_B.id)
> -> Seq Scan on table_A(cost=0.00..1941825.05 rows=95612705 width=47)
> -> Hash (cost=1220472.48..1220472.48 rows=74558048 width=20)
> -> Seq Scan on table_B(cost=0.00..1220472.48 rows=74558048 width=20)
> ----------------------------------------------------------------------
>
> The documentation says the 'cost' numbers are 'units of disk page
> fetches'.
>
> Do you, by any chance, have any notion of how many disk page fetches
> can be processed per second in practice - at least a rough idea?
>
> IOW how do I convert - guesstimate! - these numbers into (plausible)
> time values?
No chance. This are "virtual values" for planner only.
If I read correctly, your query should go into two phases: build hash
map on one table, then update second table using the map. Not that this
all valid unless you have any constraints (including foreign checks,
both sides) to check on any field of updated table. If you have, you'd
better drop them.
Anyway, this is two seq. scans. For a long query I am using a tool like
ktrace (freebsd) to get system read/write calls backend is doing. Then
with catalog tables you can map file names to relations
(tables/indexes). Then you can see which stage you are on and how fast
is it doing.
Note that partially cached tables are awful (in FreeBSD, dunno for
linux) for such a query - I suppose this is because instead on
sequential read, you get a lot of random reads that fools prefetch
logic. "dd if=table_file of=/dev/null bs=8m" helps me a lot. You can see
it it helps if CPU time goes up.
Best regards, Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 19:32 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 11:16 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 14:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2011-06-24 14:00 UTC (permalink / raw)
To: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
Harry Mantheakis <harry.mantheakis@riskcontrollimited.com> 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_cost is 1, so if
everything is tuned perfectly, the run time should match the time it
takes to sequentially read a number of pages (normally 8KB) which
matches the estimated cost. So with 8KB pages and seq_page_cost =
1, the cost number says it should take the same amount of time as a
sequential read of 130 GB.
The biggest reason this won't be close to actual run time is that is
that the planner just estimates the cost of *getting to* the correct
tuples for update, implicitly assuming that the actual cost of the
updates will be the same regardless of how you find the tuples to be
updated. So if your costs were set in perfect proportion to
reality, with seq_page_cost = 1, the above would tell you how fast a
SELECT of the data to be updated should be. The cost numbers don't
really give a clue about the time to actually UPDATE the rows.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
@ 2011-06-24 12:52 ` Mark Thornton <mthornton@optrak.com>
2011-06-24 13:43 ` Re: Long Running Update Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:31 ` Re: Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2 siblings, 2 replies; 16+ messages in thread
From: Mark Thornton @ 2011-06-24 12:52 UTC (permalink / raw)
To: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
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 updates involving a FROM clause wrong --- the resulting
table is correct but the running time is quadratic. You might want to
try a series of smaller examples to see if your query displays this
behaviour.
Mark Thornton
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 12:52 ` Re: Long Running Update Mark Thornton <mthornton@optrak.com>
@ 2011-06-24 13:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2011-06-24 13:43 UTC (permalink / raw)
To: Mark Thornton <mthornton@optrak.com>; Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>; +Cc: pgsql-performance
Mark Thornton <mthornton@optrak.com> 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 frequent way I've seen that happen is for someone to do:
UPDATE table_A
[ ... ]
FROM table_A a, table_B b
WHERE b.id = a.id
Because a FROM clause on an UPDATE statement is not in the standard,
different products have implemented this differently. In Sybase ASE
or Microsoft SQL Server you need to do the above to alias table_A,
and the two references to table_A are treated as one. In PostgreSQL
this would be two separate references and you would effectively be
doing the full update of all rows in table_A once for every row in
table_A. I don't think that is happening here based on the plan
posted earlier in the thread.
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Long Running Update
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 12:52 ` Re: Long Running Update Mark Thornton <mthornton@optrak.com>
@ 2011-06-24 16:31 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
1 sibling, 0 replies; 16+ messages in thread
From: Harry Mantheakis @ 2011-06-24 16:31 UTC (permalink / raw)
To: Mark Thornton <mthornton@optrak.com>; +Cc: pgsql-performance
> try a series of smaller examples...
Mark, that was the tip the saved me!
Many thanks.
Harry Mantheakis
London, UK
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2011-06-24 16:49 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-23 15:05 Long Running Update Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 17:18 ` Claudio Freire <klaussfreire@gmail.com>
2011-06-24 11:19 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 11:39 ` Claudio Freire <klaussfreire@gmail.com>
2011-06-24 12:39 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 15:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:29 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 16:41 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:49 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-23 19:32 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 11:16 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
2011-06-24 11:45 ` Vitalii Tymchyshyn <tivv00@gmail.com>
2011-06-24 14:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 12:52 ` Mark Thornton <mthornton@optrak.com>
2011-06-24 13:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-06-24 16:31 ` Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox