From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: hzzhangjiazhi <hzzhangjiazhi@corp.netease.com>
Cc: Gary Doades <gpd@gpdnet.co.uk>
Cc: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: effective_io_concurrency on EBS/gp2
Date: Thu, 8 Feb 2018 18:05:00 +0200
Message-ID: <4948d2de-33c1-1c98-df9e-8b5541cd7eee@gmail.com> (raw)
In-Reply-To: <CAGTBQpaOsC4=hwtO-ZkE9XVuUS2DqfiLw3-Zf_tjj1CfT49fjw@mail.gmail.com>
References: <6c7a45df-f6ab-f2ce-6f84-9555864f6c86@gmail.com>
<CAMAYy4+SPqTLuwjWLrnrAnZ2EVyz97oQ2Hey46fffP8tihaRXQ@mail.gmail.com>
<768e3a56-46ed-3dd1-8722-953a47857a35@gmail.com>
<014801d39a99$d4c32930$7e497b90$@gpdnet.co.uk>
<8fb758a1-d7fa-4dcc-fb5b-07a992ae6a32@gmail.com>
<CAGTBQpYCWStnzsjTrN+vBsEwZHH2PKwO7ybxp_JQv9CsEJj4xw@mail.gmail.com>
<534b557c-3658-6d0f-2330-34a78da57e2f@gmail.com>
<9122FC8B-2129-4E10-807F-F9F34117A156@corp.netease.com>
<CAGTBQpZjj1ekDLyZ=RdyPhKAbLnow1ma66TDuBj=eradrgOskQ@mail.gmail.com>
<c8b0b311-c436-622a-c5fa-96718ead36a0@gmail.com>
<85a21d95-a776-4c1c-3223-5568864df3f3@gmail.com>
<CAGTBQpZyXSxPuCFLBR3eg95=Lk3MexzaQdCGkuvH+vdKnN6gHg@mail.gmail.com>
<7895b3f0-0871-2b04-d3a3-3ade175e7212@gmail.com>
<CAGTBQpaOsC4=hwtO-ZkE9XVuUS2DqfiLw3-Zf_tjj1CfT49fjw@mail.gmail.com>
>> Would a bitmap scan over expression index ((aid%1000)=0) do a sparse bitmap
>> scan?
> If you have a minimally correlated index (ie: totally random order),
> and suppose you have N tuples per page, you need to select less (much
> less) than 1/Nth of the table.
>
I've done a test with a sparse bitmap scan. The positive effect of
effective_io_concurrency is visible in that case.
In the test, I'm creating a table with 100k rows, 10 tuples per page.
Then I create an index on expression ((id%100)=0), and then query the
table using a bitmap scan over this index. Before each query, I also
restart postgresql service and clear OS caches, to make all reads happen
from disk.
create table test as select generate_series(1, 100000) id, repeat('x',
750) val;
create index sparse_idx on test (((id%100)=0));
explain (analyze, buffers) select * from test where ((id%100)=0) and val
!= '';
effective_io_concurrency=0 Execution time: 3258.220 ms
effective_io_concurrency=1 Execution time: 3345.689 ms
effective_io_concurrency=2 Execution time: 2516.558 ms
effective_io_concurrency=4 Execution time: 1816.150 ms
effective_io_concurrency=8 Execution time: 1083.018 ms
effective_io_concurrency=16 Execution time: 2349.064 ms
effective_io_concurrency=32 Execution time: 771.776 ms
effective_io_concurrency=64 Execution time: 1536.146 ms
effective_io_concurrency=128 Execution time: 560.471 ms
effective_io_concurrency=256 Execution time: 404.113 ms
effective_io_concurrency=512 Execution time: 318.271 ms
effective_io_concurrency=1000 Execution time: 411.978 ms
effective_io_concurrency=0 Execution time: 3655.124 ms
effective_io_concurrency=1 Execution time: 3337.614 ms
effective_io_concurrency=2 Execution time: 2914.609 ms
effective_io_concurrency=4 Execution time: 2133.285 ms
effective_io_concurrency=8 Execution time: 1326.740 ms
effective_io_concurrency=16 Execution time: 1765.848 ms
effective_io_concurrency=32 Execution time: 583.176 ms
effective_io_concurrency=64 Execution time: 541.667 ms
effective_io_concurrency=128 Execution time: 362.409 ms
effective_io_concurrency=256 Execution time: 446.026 ms
effective_io_concurrency=512 Execution time: 416.469 ms
effective_io_concurrency=1000 Execution time: 301.295 ms
effective_io_concurrency=0 Execution time: 4611.075 ms
effective_io_concurrency=1 Execution time: 3583.286 ms
effective_io_concurrency=2 Execution time: 2404.817 ms
effective_io_concurrency=4 Execution time: 1602.766 ms
effective_io_concurrency=8 Execution time: 1811.409 ms
effective_io_concurrency=16 Execution time: 1688.752 ms
effective_io_concurrency=32 Execution time: 613.454 ms
effective_io_concurrency=64 Execution time: 686.325 ms
effective_io_concurrency=128 Execution time: 425.590 ms
effective_io_concurrency=256 Execution time: 1394.318 ms
effective_io_concurrency=512 Execution time: 1579.458 ms
effective_io_concurrency=1000 Execution time: 414.184 ms
Regards,
Vitaliy
Attachments:
[application/octet-stream] sparse.zip (4.3K, ../4948d2de-33c1-1c98-df9e-8b5541cd7eee@gmail.com/2-sparse.zip)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vgarnashevich@gmail.com, klaussfreire@gmail.com, hzzhangjiazhi@corp.netease.com, gpd@gpdnet.co.uk, rottenwindfish@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: effective_io_concurrency on EBS/gp2
In-Reply-To: <4948d2de-33c1-1c98-df9e-8b5541cd7eee@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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