Re: GiST "choose subtree" support function to inline penalty

On Thu, Jun 27, 2019 at 6:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?Q?Darafei_=22Kom=D1=8Fpa=22_Praliaskouski?= <me(at)komzpa(dot)net>
> writes:
> > I'm looking at PostGIS geometry GiST index build times and try to
> optimize
> > withing the current GiST framework. The function that shows a lot on my
> > flame graphs is penalty.
>
> > I spent weekend rewriting PostGIS penalty to be as fast as possible.
> > (FYI https://github.com/postgis/postgis/pull/425/files)
>
> > However I cannot get any meaningfully faster build time. Even when I
> strip
> > it to "just return edge extension" index build time is the same.
>
> TBH this makes me wonder whether the real problem isn't so much "penalty
> function is too slow" as "penalty function is resulting in really bad
> index splits".
>

As an extension writer I don't have much control on how Postgres calls
penalty function. PostGIS box is using floats instead of doubles, so its
size is twice as small as postgres builtin box, meaning penalty is called
even more often on better packed pages.

I can get index construction speed to be much faster if I break penalty to
actually result in horrible splits: index size grows 50%, construction is
30% faster.

>
> It might be that giving the opclass higher-level control over the split
> decision can help with both aspects.

Please note the question is not about split. Korotkov's split is working
fine. Issue is with penalty and computations required for choosing the
subtree before split happens.

Andrey Borodin proposed off-list that we can provide our own index type
that is a copy of GiST but with penalty inlined into "choose subtree" code
path, as that seems to be the only way to do it in PG12. Is there a more
humane option than forking GiST?

> But never start micro-optimizing
> an algorithm until you're sure it's the right algorithm.
>

That's exactly the reason I write original letter. I don't see any option
for further optimization in existing GiST framework, but this optimization
is needed: waiting 10 hours for GiST to build after an hour of ingesting
the dataset is frustrating, especially when you see a nearby b-tree done in
an hour.

--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa