Re: Improving the Performance of Full Table Updates

Hi Tom,
Thanks for the feedback. Let me clarify my intention again.
This was thought for improving the performance of the Data Warehousing
applications

Full table updates similar to
"Update dd set n2=n2+1"

When you talked about firing triggers, i looked into the implementation of
triggers and the approach i suggested may not work fine with the Triggers.
Since we cannot hold write locks and fire triggers, triggers should get
disabled for this. But Remember in Data Warehousing applications, people
won't be usually having Row-level triggers.

My alternate suggestion would be to make this kind of update an optional one
to provide the speed up.

If a page contains 100 rows, then the current scenario takes 1 read lock +
100 write locks to complete the full table update. In the suggested
scenario, it takes one Write Lock. Also it reduces the 101 Logical I/Os to1
Logical I/O. This might provide the same kind of benefit the Bitmap Index
Scan provided.

Again there are some specific cases
a) If the tuple is locked / Concurrently being updated:
Currently we release the buffer, take a lock on the
tuple and wait for the transaction to complete in case of concurrent
updates. In the Full table update also, we will do the same.

b) If the page contains lot of deleted tuples:
This is a tricky scenario. Say if we have 100 tuples
and we have 20% of them deleted. In the current scenario, we will find that
out during the read lock and we will not waste time in those tuples during
the write lock. But in the suggested scenario, we will be wasting time in
those with the write lock on the buffer. In order to circumvent that, we can
resort to a one read lock + one write lock combination.

Again if this full table updates are thought with the OLTP applications in
mind, then this is not at all a suitable option. This will only benefit the
people with Data Warehouses.

Expecting some more replies....

Thanks,
Gokul.

On 9/20/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Gokulakannan Somsundaram" <gokul007(at)gmail(dot)com> writes:
> > I propose to change this row-by-row approach, when it is a full table
> > update. I plan to send a extra flag(which will be set for Full table
> > Deletes/Updates). this would make the access method directly acquire the
> > exclusive lock and update the existing record.
>
> This sounds like a recipe for utter destruction of what little
> modularity and layering we've got left. And I rather doubt it will buy
> anything interesting performance-wise.
>
> To cite just one concrete objection: surely the tuple-fetch code has got
> no business firing triggers.
>
> regards, tom lane
>