Extra tables is not something that will work out in my workflow...
I've managed to cut the time in half already, but perhaps there's more to
be won.
In one of the calculations done on the st_area, I used a log(base,value),
but since the only 2-param log function present in postgres takes numerics
a
Why not create a table with cols a, b, c and d. Where you insert a row for
each combination and key and index abc then return d?
ons. 24. feb. 2021, 21:15 skrev Paul van der Linden <
paul.doskabou...@gmail.com>:
> Thanks for all the suggestions,
>
> When the server is not in use for mission-criti
Thanks for all the suggestions,
When the server is not in use for mission-critical work, I'll definitely
going to do some testing based on your ideas.
Will let you know what comes out of that
Cheers,
Paul
On Thu, Feb 18, 2021 at 6:49 PM hubert depesz lubaczewski
wrote:
> On Thu, Feb 18, 2021 a
On 2021-02-16 19:30:23 +0100, Paul van der Linden wrote:
> I have 2 functions:
>
> CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
> $func$
> DECLARE
> retVal text;
> BEGIN
> SELECT
> CASE
> ... snip long list containing various tests on a,b and c
> END INTO
On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote:
> [1]https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
>
> Thanks for this reference. I enjoy your blog, but haven't made the time to
> read all the archives somehow. Stuff doesn't stick very
> well when it isn
>
> https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/
Thanks for this reference. I enjoy your blog, but haven't made the time to
read all the archives somehow. Stuff doesn't stick very well when it isn't
yet "needed" info besides.
I have seen overhead from 'raise notice' in
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 th
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.
On Wed, Feb 17, 2021 at 7:48 PM hubert depesz lubaczewski
wrote:
> On Tue, Feb 16,
On Tue, Feb 16, 2021 at 07:30:23PM +0100, Paul van der Linden wrote:
> Hi,
> I have 2 functions:
> CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
> $func$
> DECLARE
> retVal text;
> BEGIN
> SELECT
> CASE
> WHEN a='v1' AND b='b1' THEN 'r1'
> WHEN a='v1' THE
On 2/16/21 12:30 PM, Paul van der Linden wrote:
Hi,
I have 2 functions:
CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
$func$
DECLARE
retVal text;
BEGIN
SELECT
CASE
WHEN a='v1' AND b='b1' THEN 'r1'
WHEN a='v1' THEN 'r2'
... snip long list contai
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 kn
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_work
Hi,
I have 2 functions:
CREATE FUNCTION foo(a text, b text, c text) RETURNS text AS
$func$
DECLARE
retVal text;
BEGIN
SELECT
CASE
WHEN a='v1' AND b='b1' THEN 'r1'
WHEN a='v1' THEN 'r2'
... snip long list containing various tests on a,b and c
WHEN a='v5
13 matches
Mail list logo
