On Wed, Feb 17, 2021 at 08:40:17PM +0100, Paul van der Linden wrote: > The st_area calculation is done mostly once or sometimes twice for each geom, > and I suspect that can't explain the factor 20 slower. > Creating an index with only one st_area calculation is also done rather > quickly.
In this case, make small test case, like 1000 rows, or something like this. run create index without where, and then remake the functions with profiling info, for example using this approach: https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ and check for yourself what takes this time, and if it's something you can fix. Best regards, depesz
