On Wed, Feb 5, 2014 at 5:29 PM, Heikki Linnakangas <[email protected]> wrote: > On 01/30/2014 08:53 AM, Amit Kapila wrote: >> >> Is it possible to do for both prefix and suffix together, basically >> the question I >> have in mind is what will be deciding factor for switching from hash table >> mechanism to string comparison mode for suffix. Do we switch when we find >> long enough match? > > > I think you got it backwards. You don't switch from hash table mechanism to > string comparison. You do the prefix/suffix comparison *first*, and run the > hash table algorithm only on the "middle" part, between the common prefix > and suffix. > > >> Can we do this optimization after the basic version is acceptable? > > > I would actually suggest doing that first. Perhaps even ditch the whole > history table approach and do *only* the scan for prefix and suffix. That's > very cheap, and already covers a large fraction of UPDATEs that real > applications do. In particular, it's optimal for the case that you update > only a single column, something like "UPDATE foo SET bar = bar + 1". > > I'm pretty sure the overhead of that would be negligible, so we could always > enable it. There are certainly a lot of scenarios where prefix/suffix > detection alone wouldn't help, but so what. > > Attached is a quick patch for that, if you want to test it.
I have done one test where there is a large suffix match, but
not large enough that it can compress more than 75% of string,
the CPU overhead with wal-update-prefix-suffix-encode-1.patch is
not much, but there is no I/O reduction as well. However for same
case there is both significant WAL reduction and CPU gain with
pgrb_delta_encoding_v8.patch
I have updated "ten long fields, all changed" such that there is large
suffix match. Updated script is attached.
Unpatched
testname | wal_generated | duration
------------------------------+---------------+------------------
ten long fields, all changed | 1760986528 | 28.3700430393219
ten long fields, all changed | 1760981320 | 28.53244805336
ten long fields, all changed | 1764294992 | 28.6722140312195
(3 rows)
wal-update-prefix-suffix-encode-1.patch
testname | wal_generated | duration
------------------------------+---------------+------------------
ten long fields, all changed | 1760986016 | 29.4183659553528
ten long fields, all changed | 1760981904 | 29.7636449337006
ten long fields, all changed | 1762436104 | 29.508908033371
(3 rows)
pgrb_delta_encoding_v8.patch
testname | wal_generated | duration
------------------------------+---------------+------------------
ten long fields, all changed | 733969304 | 23.916286945343
ten long fields, all changed | 733977040 | 23.6019561290741
ten long fields, all changed | 737384632 | 24.2645490169525
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
wal-update-testsuite.sh
Description: Bourne shell script
-- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
