Re: pgsql 10: hash indexes testing

On Wed, Jul 05, 2017 at 05:52:32PM +0530, Amit Kapila wrote:
> >> > version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
> >> > ---------+--------------+----------------+--------------+--------------+------------+------------+------------------
> >> > 3 | 10485760 | 2131192 | 66 | 0 | 2975444240 | 0 | 1065.19942179026
> >> > (1 row)
...
> >> > And I do appear to have an odd percentage of free space. :)
>
> Are you worried about "unused_pages"? If so, then this is not a major

Nope. "free_percent" Just a bit weird that I have it at over 1000% free. :)
Shouldn't that number be < 100?

> reason to worry, because these are probably freed overflow pages which
> can be used in future. In the hash index, when we free the overflow
> pages, they are not returned back to OS, rather they are tracked in
> the index as unused pages which will get used when required in future.

> >> It looks like Vacuum hasn't been triggered.
>
> Vacuum won't be triggered on insert load. I think that is one of the
> reasons why in your initial copy, you might have got the error. We
> had some discussion in the past to trigger Vacuum on insert heavy
> workloads [1], but the patch still didn't get committed. I think if
> that patch or some other form of that patch gets committed, it will
> help the workload what you are trying here.

Well, if this is the cause of my little issue, it might be nice. ATM
my import script bombs out on errors (that I've duplicated! :) It took
11 hours but it bombed) and it sounds like I'll need to do a manual
VACUUM before it can be run again.

The stats you were looking for before are:

# select * from pgstathashindex('link_datum_id_idx');
version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
---------+--------------+----------------+--------------+--------------+------------+------------+------------------
3 | 8559258 | 4194176 | 128 | 1926502 | 3591812743 | 0 | 942.873199357466
(1 row)

# select 4194176.0/128/8;
?column?
-----------------------
4095.8750000000000000
(1 row)

If you need more info or whatnot, shout. I've a problematic index to
play with now.

> [1] - https://www.postgresql.org/message-id/b970f20f-f096-2d3a-6c6d-ee887bd30cfb%402ndquadrant.fr

AP