Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits

On 21.08.2020 19:43, Ibrar Ahmed wrote:
>
>
> On Wed, Aug 19, 2020 at 6:15 PM Anastasia Lubennikova 
> <a.lubennikova@postgrespro.ru <mailto:a.lubennikova@postgrespro.ru>> 
> wrote:
>
>     On 18.08.2020 02:54, Alvaro Herrera wrote:
>     > On 2020-Aug-14, Ibrar Ahmed wrote:
>     >
>     >> The table used for the test contains three columns (integer, text,
>     >> varchar).
>     >> The total number of rows is 10000000 in total.
>     >>
>     >> Unpatched (Master: 92c12e46d5f1e25fc85608a6d6a19b8f5ea02600)
>     >> COPY: 9069.432 ms vacuum; 2567.961ms
>     >> COPY: 9004.533 ms vacuum: 2553.075ms
>     >> COPY: 8832.422 ms vacuum: 2540.742ms
>     >>
>     >> Patched (Master: 92c12e46d5f1e25fc85608a6d6a19b8f5ea02600)
>     >> COPY: 10031.723 ms vacuum: 127.524 ms
>     >> COPY: 9985.109  ms vacuum: 39.953 ms
>     >> COPY: 9283.373  ms vacuum: 37.137 ms
>     >>
>     >> Time to take the copy slightly increased but the vacuum time
>     significantly
>     >> decrease.
>     > "Slightly"?  It seems quite a large performance drop to me --
>     more than
>     > 10%.  Where is that time being spent?  Andres said in [1] that he
>     > thought the performance shouldn't be affected noticeably, but this
>     > doesn't seem to hold true.  As I understand, the idea was that there
>     > would be little or no additional WAL records .. only flags in the
>     > existing record.  So what is happening?
>     >
>     > [1]
>     https://postgr.es/m/20190408010427.4l63qr7h2fjcyp77@alap3.anarazel.de
>
>     I agree that 10% performance drop is not what we expect with this
>     patch.
>     Ibrar, can you share more info about your tests? I'd like to
>     reproduce
>     this slowdown and fix it, if necessary.
>
>
> Here is my test;
>
> postgres=# BEGIN;
>
> BEGIN
>
>
> postgres=*# TRUNCATE foo;
>
> TRUNCATE TABLE
>
>
> postgres=*# COPY foo(id, name, address) FROM '/home/ibrar/bar.csv' 
> DELIMITER ',' FREEZE;
>
> COPY 10000000
>
>
>
> -- 
> Ibrar Ahmed


I've repeated the test and didn't notice any slowdown for COPY FREEZE.
Test data is here [1].

The numbers do fluctuate a bit, but there is no dramatic difference 
between master and patched version. So I assume that the performance 
drop in your test has something to do with the measurement error. 
Unless, you have some non-default configuration that could affect it.

patched:

COPY: 12327,090 ms vacuum: 37,555 ms
COPY: 12939,540 ms vacuum: 35,703 ms
COPY: 12245,819 ms vacuum: 36,273 ms

master:
COPY
COPY: 13253,605 ms vacuum: 3592,849 ms
COPY: 12619,428 ms vacuum: 4253,836 ms
COPY: 12512,940 ms vacuum: 4009,847 ms

I also slightly cleaned up comments, so the new version of the patch is 
attached. As this is just a performance optimization documentation is 
not needed. It would be great, if other reviewers could run some 
independent performance tests, as I believe that this patch is ready for 
committer.

[1] https://drive.google.com/file/d/11r19NX6yyPjvxdDub8Ce-kmApRurp4Nx/view

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Companyt