Thank you for your information.
Here is the result:
After insertions:
ycsb=# select * from pgstatindex('usertable_pkey');
version | tree_level | index_size | root_block_no | internal_pages |
leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 3 | 5488721920 | 44337 | 4464 |
665545 | 0 | 0 | 52 | 11
(1 row)
After rebuild:
ycsb=# select * from pgstatindex('usertable_pkey');
version | tree_level | index_size | root_block_no | internal_pages |
leaf_pages | empty_pages | deleted_pages | avg_leaf_density |
leaf_fragmentation
---------+------------+------------+---------------+--------
--------+------------+-------------+---------------+--------
----------+--------------------
2 | 3 | 3154296832 | 41827 | 1899 |
383146 | 0 | 0 | 90.08 | 0
It seems like that rebuild has an effect to reduce the number of internal
and leaf_pages and make more dense leaf pages.
On Wed, Aug 10, 2016 at 6:47 PM, Lukas Fittl <[email protected]> wrote:
> On Wed, Aug 10, 2016 at 4:24 PM, Kisung Kim <[email protected]> wrote:
>>
>> When I used the index bloating estimation script in
>> https://github.com/ioguix/pgsql-bloat-estimation,
>> the result is as follows:
>>
>
> Regardless of the issue at hand, it might make sense to verify these
> statistics using pgstattuple - those bloat estimation queries can be wildly
> off at times.
>
> See also https://www.postgresql.org/docs/9.5/static/pgstattuple.html as
> well as https://www.keithf4.com/checking-for-postgresql-bloat/
>
> Best,
> Lukas
>
> --
> Lukas Fittl
>
> Skype: lfittl
> Phone: +1 415 321 0630
>
--
Bitnine Global Inc., Kisung Kim, Ph.D
https://sites.google.com/site/kisungresearch/
E-mail : [email protected]
Office phone : 070-4800-5890, 408-606-8602
US Mobile phone : 408-805-2192
