Subject: Re: effective_io_concurrency on EBS/gp2
To: Claudio Freire <klaussfreire@gmail.com>
Cc: hzzhangjiazhi <hzzhangjiazhi@corp.netease.com>,
 Gary Doades <gpd@gpdnet.co.uk>, Rick Otten <rottenwindfish@gmail.com>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
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>
From: Vitaliy Garnashevich <vgarnashevich@gmail.com>
Message-ID: <4948d2de-33c1-1c98-df9e-8b5541cd7eee@gmail.com>
Date: Thu, 8 Feb 2018 18:05:00 +0200
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAGTBQpaOsC4=hwtO-ZkE9XVuUS2DqfiLw3-Zf_tjj1CfT49fjw@mail.gmail.com>
Content-Type: multipart/mixed;
 boundary="------------1E39A9BD6A34816CE398CFBE"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------1E39A9BD6A34816CE398CFBE
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


>> 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


--------------1E39A9BD6A34816CE398CFBE
Content-Type: application/octet-stream;
 name="sparse.zip"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="sparse.zip"

UEsDBBQAAAAIAAaASEwPqh4PxgEAAEQJAAALAAAAc3VtbWFyeS50eHS9lbFu2zAQhnc9BUcb
iA488u5ItujYrUOLorMBq4whwJYCWUqdpc8eOsnGRMFlCDcJ+Ej+vI/Hcz7mbjb3eTr347DZ
fm3MR8YLbz4ymvazR2N+juf5MOXfv36YBAJiyt4vUXZC7V3XHvthubSHYbkx3Xi664/5r9k/
mEPXmc2f/TLMi2EgsK0sT1/4HwUsAW2f/xtnUazYdGPKhPt+bjZopvHftmny7W057/4+7/px
141Dt0xTHrqHb9Z8v+Ruma+nOPen/MV4xxGcs+Z0fhvDGvPEIDGtYq7CHJcMzHEVowrDWDDk
9U3GGrPRg8X11VDqXXpKYIVWOV+nCwEhBFnF5JV07Es6WufQ1flYLFDA9RpwnY+KRYh+lWOs
83ksqryzHlpr6wURIYVrHXRuCjOgI7WbPoAgad1MSFCuk9JNh96Di6x10zuBQFbrJgZhiBS1
bnK5Cqh3kwlBJKjV9OKAbNKrSaXHOVGrSaVBkCS9mt4iuMRaNUmK0jawVs1rGVwUrZrXGxsx
KNUsz4ODIKJVMyK+W7zX1JQYIbDTqinogZi0akoUKK+XWk1yDJysWk30iaC0QLWbyCGVgFEv
J2Fp1PHpZJpHUEsDBBQAAAAIAIl+SEzSZf9+GQIAAIEFAAAOAAAAdGVzdC1zcGFyc2Uuc2jN
VMtu2zAQvOsrJmoKS64dPRynqQ3nlq9IAoOi1jYBmVRIypXa9N+7soM4AeIeiqAoDwLE5c7M
zi756SwplE4K4TZBYBu99OQ8ohg/A+CxIdstwvMs5J+VsVBQGiky5LjENbIrTHJcXSLLr5FP
rzDNcmRpmmKO0gTg5cjulCTUxvm1JfdYgb9eWH8Id1rOQXJjMMENktoambjOJbttUlpTL6WQ
G3JzuIqoxnSfVPcovc6ywJgwlggdedBqRdKrHS2VWUqjZWMtadktzhXn9we0KCqO6pJaJ4Ve
mNXqTcTR43Gf2roSXG4ktKi6HzRC0TCDdTHO98bM2RUuU1PwKwikJeGpLJ51BcE7Ig9n4Huu
fQjCMX3FqrEmTZajrMEqclE2YiP7FUOVI/as5txo0A5G+DrlzZ2omP80yb5IuFpYxxWXLYw+
UEZRpMrPGQMv0jhmjPdAnkVJ02gfDWP4pl7WZJe1WBNW1mwPWGtrmhpFh4h9L2czT62fzWqj
tI/v0gcYW5Ll+BGoJCdRqa3ymDL3y8S9UA5fwX/fkCW81guhy750nC0wGIS46c9l2YVv/cdA
5R8HNemh/uTujmdJGR3Fc05xzXYrbHfIWXO7Mb5FeGqkn25bko1XfVPVlkIcfcATuFMVxnVP
N3DJxTA6eTPu0vG3hy/xxfBeJ/dZMsDNayHPFzMM327/rbz8/5Y3+Ufygv5dOz4UvwFQSwME
FAAAAAgA6H5ITDtHkGaGAwAAeyoAAAoAAAB0ZXN0MTEudHh07ZrLrtMwEIb3fQqzQJwiOvI9
9oEicRVICHERC1YopC5EhASSFApPz5+Ui0ABKYjIIsosOlEd1xmPv86MnSa0LOz3IWvzD+FF
Xr3IqjI71HUos09bfpX17WX6skBjuQvHJkvLbbXf/9TShPc/vg/Hd0Wal+wsLdPi0+dwhb08
YIC6WaNLgYHYZbavq7esDU3LPr4OdWBnZ/nuouB8veVrlpY79iEt2IUtu3Tp6or9rTx+dufJ
c/bowY2H7G9ltYkoK3Yzb9+m79i9gI+nmGBWladZY2dZ1bRbIxOSkkgIzSWuWF19bLbaO/Yx
37Wvt8qu4YasPWA22/xt2IqEVGKIlBSetBanDph4zoqqeofLdTffd/OiDfU53NL54dp1+OH8
vA3Hds1uPLzde4tdZJ2/2Jbxddfn9JA3iyp705xjDWDU/ne7ppu9//F18zqtw47VId11jaZr
3Fxn3+y83y2w74Y279K6warbHb+Zy4lzos5o7k9Pbjjkq7H8F1s1KQ9TezVo51f5OuytqtzB
4p9t6z/a+hC+3f47a2DKoyIty7x81Y9+zoymxEv2tlmxO8eQHdq8Kr82KWkcvMW7xrOTJevV
6o8kioXEiDIBiQDRcYCoNEenWYNoKEkEbu9UBBC1JyPUMIhKG7LOjwBRLiBGlAlAFJac9ERS
e1x5M2cSHRmDkNipGCFRKmQfYpBEaYTFU7kRJOqFxIgyDYlae3RwQpN0s46JirRHTOxUjJio
PFnhB0kUDn4QZkxy6hYSI8oUJCJX4wk6cKuQPOk5kygpsZaoVzFIFKhP9TCJ3CniYkxMFHZB
MaJMgGJCmjssT6UtJbOvEz3+dHoVgUSDnSI+vGGD2ffErR5BoloKxU1EmYBEVC/CJtSHC61m
XScq8l1MhIoSE53ATPNBErudpCSxI0C0S524iSgTgCgsqkOHDkYpklbOm0TFUSd2KkqdqLA9
agZJxOyjTtRjUBTy/64U/wWMcWmchEdkpsoqrGktiGs1ZxxxmzDYojrpCEAa7Bi54cMMYznp
RIzgUZqlXNxElAlYNOSTBPHCI3cTfs4oolpLkAT0KgKIziIyDu/baK7hMDUCRCOWanETUSYB
kZvufB9HGXruKaozmJleRQDRc1JmuFhUAq/ZjIqIeJzlnbdNRJmARI8qRVO/jSrnfbyPd1kM
8vBeRSDRcAzshkOiEEhMfjnJ+AJQSwMEFAAAAAgANX9ITEaSQLOOAwAAmSoAAAoAAAB0ZXN0
MTIudHh07ZrLrtMwEIb3fQqzQLQIRr7HKRSJq0BCiItYsEIhdSEiJJCkUHh6fqcFBApIQUQW
Ub3oVLVz7BnPF8+MT+s75nc7n3fFR/+yqF/mdZXvm8ZX+ecNv8b6/ip7VaKz2vpDm2fVpt7t
fupp/Ycfv/vD+zIrKrbMqqz8/MVfYa/2mKBpV3ikxETsMts19TvW+bZjn974xrPlstheFJyv
NnzFsmrLPmYlu7Bhly5dW7C/bU+e3336gj1+ePMR+9u2uBqxLditonuXvWf3PT6ewcCsro5W
Y8u8bruNkQlJSSSE5hLfWFN/ajc6dexTse3ebJRdYRvybg9rdsU7vxGSeKqJlDWKdHp6AIbn
rKzr9/i6Cva+V5Sdb9bYlrAP129gH9brzh+6Fbv56E6/W+wiC/vFNoyvwjPHRd4q6/xtu4YP
YNb+74auW/3+4+f2Tdb4LWt8tg2dJnRevcG+6fkgONh3Rdv3WdPC67aHb+py4pwISkOJ48oN
Rzspy3/RVZHjhqgXg3qe2mna23W1hcY/69Z/dM3efxv+O22gyuMyq6qiet3PvmZGkNCCvWsX
7O7B5/uuqKtTF6xvSEgdOpdHTVaLxR9JFGcSI7ZpSFSCwz2VkmStnTOJmriyRL2IQKLSJI0c
JlGphKwYQ6I8kxixTUCiJSvhzDIVAJGrOYNoiId3Ti9igJhipXYQRFhfw/rpCBD1fw3ivyAx
LoqTwIjXsbAWNAolSJt0zjRKnEs8ITrKCDw6RYKrYR6FUiSdGcGjO/M4Px4xOA2no1AS/pDM
m0dBIRino4zAo0ZOYIZ5hP0tJZqP4FHYM5DzA1IL4lbggQQFBpeImQOpQzDQyxiZIwythB0G
MrGGnHYjgFTn1PFqxDYBjMGNHfzZCURwes4oGlSLFYYHEQFECQMnbhBEE6LYxI7g0J4zx7gk
TsJiGO80XFRzsvOGEaF4ksA2vYwRqJoEEbIZxhEBirXJCByF/L8zx3+AY1QaJ2AxJW5MuGUE
inLWOaPB+wbnYhAxzkXhUtxt2N/cMkrSoyqq0pxTxrgsTkJjcGWDk1ErR8m8ccSJaBKoepQR
gHSol5lkkEetLXE5JlA14pwxXo3YJmARRTyH8VpIoDjrIFWRCoXjIGLcNVqsUqphEIVFPWnM
wYjl/N//CvcvUIzL4iQ0CoF6RkikHALV1M0ZR8FxtwBVexkjZ0RemP4mZ1Qc5Z30l7vGr1BL
AwQUAAAACAAGgEhMyba2TqUDAACaKgAACgAAAHRlc3QxMy50eHTtmkmv0zAQgO/9FeaAaBGM
vC+FILEKJIRYxIETCqkLEXnJI0mh8OsZpwUE5CEFNbISPR86URzXmeWzPXYa3xK/3fqszT/7
t3n1NqvKbFfXvsy+JvQW6erL9F2BleXG75ssLZNqu/2tpvGfft33+/MizUuyTMu0+PrN3yDv
dthB3aywSYEdketkW1dnpPVNS7588LUny2W+ucooXSV0RdJyQz6nBbmSkGvXbi3I/5YXrx++
fEOeP737jPxvWdyMWBbkXt6epefkscefV2hgUpUHq5FlVjVtorgBzgEYk5TjFamrL00inSVf
8k37IRF6hW7I2h1as83PfMI4KCkBpKYOhHOHBmh4SoqqOsfLVbD3o7xofb1GtwQ/3L6Dfliv
W79vV+Tuswedt8hVEvxFEkJXoc3hJe8VVfaxWWMMYK/d/4aqe53/8XbzIa39htQ+3YRKFSpv
3iE/9HwSAuynos15WjcYdZv9D3UpUAoQlKbHN1cUy1FZ+oeuErjRAJ3o1fNYjt3er8oNavy7
bt1PW+/8j8cv0gZVeV6kZZmX77ve10Qp0FKTs2ZBHu59tmvzqjxWSc0YUKNC5fKgyWqx+CeJ
7JLEiGUEEhVIg8EslBYguJ0ziAKsQhCDiAEipxq0YL0kCmUFcKsHkMgnTeIpUIzL4ig0CglO
YIxyiYFttZ41jgIMTk5wkBGAZJqBNrwXSHSABMvMACDlpIE8AY9RcRwBRgOGGnxeBxYpnzOL
EphF03QiAolcASYCvSAyjc4yesjMaCcN4ilIjIviKDBiiAgmsYFxDqPUzJlGzsCElDHIKDkj
sxaUov1AWkwaJXUDgGR60kSeAMioPI5Ao8bNDBemRo1To5v1MpUjgzjudCICimG4E+YCFDVy
ahQfgKKYdtp4iWLPKpUyfF6HfMXROZOowAocdDoRg0QN0tpeEDUTIJUcwKGedrZ4ChDjkjgK
i0xZoMIijZhNCcHmTCPjIeQBOhljZrQ8DHr9PFqNm9mDzjX4tLPGE/AYFccRYHQYxuHcjUtM
pMScUVSgHA46nYgAInNh6WH6jxh5eCs6gESupp0tngLFuCyOQiPGgVQUGwhHQdFZ88gMblMx
gIOMQKQ04C6YGtH+EnfS7AAiFZt20nhJ5JHIv8/fugWcMgY/EVFzJlJQ4KgvHGQEIgUeNar+
5BHt73BsHEIkvs/lJ3E3I5YRcMS5woblKuZThs36eENBGHGgExFQtBTYRatVFk5A/9jH+Q5Q
SwECPwAUAAAACAAGgEhMD6oeD8YBAABECQAACwAkAAAAAAAAACAAAAAAAAAAc3VtbWFyeS50
eHQKACAAAAAAAAEAGACAJ3Yi5aDTAYD3tWbmoNMB5hujN+Kg0wFQSwECPwAUAAAACACJfkhM
0mX/fhkCAACBBQAADgAkAAAAAAAAACAAAADvAQAAdGVzdC1zcGFyc2Uuc2gKACAAAAAAAAEA
GACAfu8H5KDTAQBQ4BDkoNMBsSAaOOKg0wFQSwECPwAUAAAACADofkhMO0eQZoYDAAB7KgAA
CgAkAAAAAAAAACAAAAA0BAAAdGVzdDExLnR4dAoAIAAAAAAAAQAYAAC6oHLkoNMBgCd2IuWg
0wGbkYw44qDTAVBLAQI/ABQAAAAIADV/SExGkkCzjgMAAJkqAAAKACQAAAAAAAAAIAAAAOIH
AAB0ZXN0MTIudHh0CgAgAAAAAAABABgAAI+myeSg0wGAJ3Yi5aDTAeV7/TjioNMBUEsBAj8A
FAAAAAgABoBITMm2tk6lAwAAmioAAAoAJAAAAAAAAAAgAAAAmAsAAHRlc3QxMy50eHQKACAA
AAAAAAEAGACAJ3Yi5aDTAYAndiLloNMBcHNxOeKg0wFQSwUGAAAAAAUABQDRAQAAZQ8AAAAA

--------------1E39A9BD6A34816CE398CFBE--