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@xxxxxxxxxxx> 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.
