Re: Slow index creation

Well, first off it's annoying if I have to change the function and a
reindex afterwards, and secondly, lots of other queries are blocking on
that reindex query (basically everything needing a queryplan on that table).

Materializing is also an option but that too is taking its time.

As far as I know there's no parallelism used currently, and as per
documentation, only creating b-tree indices support parallelism..
Also my postgres installation (on windows) doesn't seem to do anything with
JIT (even after setting all the jit-related values to 0)

I was more trying to get a feeling on where the slowness is, and how to
improve that...

On Tue, Feb 16, 2021 at 7:45 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> What is your concern with it taking 20 hours vs 1 hour? Is this index
> re-created on a regular basis?
>
> Would it make any sense to materialize the value of foo(a,b,c) as a
> generated column (PG12+ natively, or maintained by a trigger before)? Or
> even bar(foo(a,b,c),geom)?
>
> Do you know if parallel_workers are being used?
>
> JIT is available in PG11, it is just off by default. If it is available,
> turning it on and trying it seems like the simplest check if it would speed
> up the index creation.
>