Re: pgsql 10: hash indexes testing

On Tue, Jul 11, 2017 at 8:10 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Amit Kapila wrote:
>> On Tue, Jul 11, 2017 at 6:51 AM, AP <ap(at)zip(dot)com(dot)au> wrote:
>> > On Fri, Jul 07, 2017 at 05:58:25PM +0530, Amit Kapila wrote:
>
>> >> I can understand your concerns. To address first concern we need to
>> >> work on one or more of following work items: (a) work on vacuums that
>> >> can be triggered on insert only workload (it should perform index
>> >> vacuum as well) (b) separate utility statement/function to squeeze
>> >> hash index (c) db internally does squeezing like after each split, so
>> >> that chances of such a problem can be reduced, but that will be at the
>> >> cost of performance reduction in other workloads, so not sure if it is
>> >> advisable. Among these (b) is simplest to do but may not be
>> >> convenient for the user.
>> >
>> > (a) seems like a good compromise on (c) if it can be done without disruption
>> > and in time.
>> > (b) seems analogous to the path autovcauum took. Unless I misremember, before
>> > autovacuum we had a cronjob to do similar work. It's probably a sane path
>> > to take as a first step on the way to (a)
>> > (c) may not be worth the effort if it compromises general use, though perhaps
>> > it could be used to indicate to (a) that now is a good time to handle
>> > this bit?
>>
>> Nice summarization! I think before doing anything of that sort we
>> need opinions from others as well. If some other community members
>> also see value in doing one or multiple of above things, then I can
>> write a patch.
>
> I haven't read the thread, but in late PG10 autovacuum gained the idea
> of "work items" that can be plugged from other parts of the server;
> currently BRIN uses it to cause a range to be summarized right after the
> next one starts being filled. This is activated separately for each
> index via a reloption. Perhaps something like that can be used for hash
> indexes? See commit 7526e10224f0792201e99631567bbe44492bbde4.
>

Yes, I also think the same idea can be used, in fact, I have mentioned
it [1] as soon as you have committed that patch. Do we want to do
anything at this stage for PG-10? I don't think we should attempt
something this late unless people feel this is a show-stopper issue
for usage of hash indexes. If required, I think a separate function
can be provided to allow users to perform squeeze operation.

[1] - https://www.postgresql.org/message-id/CAA4eK1%2BnVxAGmzj7RPUHscbTdAG1zQub6L9UqFJSu%3DgdwtY%2BpQ%40mail.gmail.com

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com