On 7/9/2007 3:18 PM, Jeff Davis wrote:
> On Mon, 2007-07-09 at 08:46 -0400, Jan Wieck wrote:
>> > Why does this not apply to UPDATEs as well? 
>> 
>> I can see how multiple log rows for DELETE might be combined into one 
>> subscriber side DELETE statement with a WHERE clause using IN (BETWEEN 
>> would be difficult in the case of multi-column primary keys).
>> 
>> How you intend to do the same for INSERT or UPDATE is unclear to me. As 
>> said before, there is no SQL statement logging available and even if it 
>> where, due to MVCC it won't do us any good because Slony does not 
>> replicate single transactions in their exact commit order. This 
>> visibility problem is one of the tricky details that killed pgcluster 1. 
>> I won't let it sneak into Slony.
> 
> I am _not_ suggesting statement replication in Slony, and that was not
> the direction I intended to take this discussion (statement replication
> is really more the domain of pgpool, which is interesting but a
> completely different design).
> 
> As I understand it, the subscriber reads the log from the provider and
> turns the log into individual statements on the subscriber.
> 
> Instead, the subscriber could read the log on the provider into a temp
> table on the subscriber, then the subscriber could do a "DELETE FROM foo
> WHERE ... IN (select ... from temp_table)" or an "UPDATE ... SET ...
> FROM temp_table WHERE ...". This should not be any different than
> running individual statements within a single transaction, but is more
> efficient in some cases (bulk deletes/updates).
> 
> In the quick test I just ran (attached ruby script), it took 160s to
> COPY the 5M updates into a temp table, and then run one UPDATE that
> would update every record in the table by joining with the temp table
> (the COPY was a few seconds, so most of the 160s was the single UPDATE
> statement). That's way faster than the 15 minutes it took to update one
> record at a time.

Although the ruby script is missing, I think I get the picture. This 
would work if the log trigger would record every column of every updated 
row. But it doesn't do that. I only logs the columns that have a new 
value that is distinct from the old one.

The speed gain in your test has a couple of sources. The parsing 
overhead of 5 million update statements vs one copy is one of them, the 
network latency for 5 million round trips another and the execution plan 
(which only applies if all or at least a substantial part of the entire 
table is updated) of course. Sure is one such mega update of the entire 
table better done in a merge over the two sorted tuple sets. But the 
planner might chose another strategy if you updated only 500,000 out of 
10 million rows.

It would be interesting to see how 5 million updates done via prepared 
statement using parameters fare in the comparison. Those mass updates 
would at least tend to use a fairly small number of different column 
combinations (this might even be generally true for an entire 
application). Using prepared statements to apply the changes will deal 
with some part of the parsing and planning overhead. It will still force 
your case to do 5 million index scans instead of two sorts and a merge. 
But that comparison really only applies to cases where you update a very 
substantial part of the whole table.

There might be a completely different possibility of dealing with the 
problem. Unfortunately I will not have the time to implement it. But the 
idea goes as follows.

The log is selected by actually doing a COPY over a SELECT (the usual 
log select). That COPY result is fed into the current log table on the 
subscriber. A trigger on that log table will suppress the actual insert 
operation if the subscriber is not in forwarding mode and the operation 
is for a subscribed table (the node might be origin to something else). 
What it also does is doing the actual leg work of applying the changes 
via prepared SPI statements or maybe even direct heap and index updates. 
This method cuts down on the parsing and planning, as well as on the 
network round trips.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #