Re: Reduce pinning in btree indexes

Hi,

At Fri, 27 Feb 2015 05:56:18 +0000, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote in <874mq77vuu(dot)fsf(at)news-spur(dot)riddles(dot)org(dot)uk>
> >>>>> "Kyotaro" == Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> writes:
>
> Kyotaro> ammarkpos/amrestrpos are called in merge joins. By the steps
> Kyotaro> shown below, I had 1M times of markpos and no restorepos for
> Kyotaro> 1M result rows, and had 500k times of markpos and the same
> Kyotaro> number of times of restorepos for 2M rows result by a bit
> Kyotaro> different configuration. I suppose we can say that they are
> Kyotaro> the worst case considering maskpos/restrpos. The call counts
> Kyotaro> can be taken using the attached patch.
>
> You might want to try running the same test, but after patching
> ExecSupportsMarkRestore to return false for index scans. This will cause
> the planner to insert a Materialize node to handle the mark/restore.

Mmm? The patch bt-nopin-v1.patch seems not contain the change for
ExecSupportMarkRestore and the very simple function remain
looking to return true for T_Index(Only)Scan after the patch
applied.

Explain shows that the plan does not involve materializing step
and addition to it, the counters I put into both btmarkpos() and
btrestrpos() showed that they are actually called during the
execution, like this, for both unpatched and patched.

| LOG: markpos=1000000, restrpos=0
| STATEMENT: EXPLAIN (ANALYZE,BUFFERS) SELECT t1.a, t2.a FROM t1 JOIN t2 on (t1.a = t2.a);

To make sure, I looked into btmarkpos and btrestrpos to confirm
that they really does the memcpy() we're talking about, then
recompiled whole the source tree.

> This way, you can get an idea of how much gain (if any) the direct
> support of mark/restore in the scan is actually providing.

Does "the scan" mean T_Material node? But no material in plan and
counters in bt*pos were incremented in fact as mentioned above..

Could you point out any other possible mistake in my thought?

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center