RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> I tried this scenario using edb's 13.3 x64 install: > > postgres=# select version(); >version > > PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit > (1 row) > > > postgres=# \l postgres >List of databases > Name | Owner | Encoding | Collate | Ctype | Access privileges > --+--+--+-+---+--- > postgres | postgres | UTF8 | C | C | > (1 row) > > postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select > MAX(toFloat(a, null)) as "a" from sampletest; > QUERY PLAN > - > --- > Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual > time=44962.279..44962.280 rows=1 loops=1) > Buffers: shared hit=657 > -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 > width=15) (actual time=0.009..8.900 rows=10 loops=1) > Buffers: shared hit=637 > Planning: > Buffers: shared hit=78 > Planning Time: 0.531 ms > Execution Time: 44963.747 ms > (8 rows) > > and with locally compiled REL_13_STABLE's head on the same machine: > > rjuju=# select version(); >version > > PostgreSQL 13.4, compiled by Visual C++ build 1929, 64-bit > (1 row) > > rjuju=# \l rjuju > List of databases Name | Owner | Encoding | Collate | > Ctype | Access privileges > ---+---+--+-+---+--- > rjuju | rjuju | UTF8 | C | C | > (1 row) > > rjuju-# select MAX(toFloat(a, null)) as "a" from sampletest; >QUERY PLAN > - > -- > Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual > time=460.334..460.334 rows=1 loops=1) > Buffers: shared hit=646 read=1 > -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 > width=32) (actual time=0.010..7.612 rows=10 loops=1) > Buffers: shared hit=637 > Planning: > Buffers: shared hit=20 read=1 > Planning Time: 0.125 ms > Execution Time: 460.527 ms > (8 rows) > > Note that I followed [1], so I simply used "build" and "install". I have no > idea what is done by default and if NLS is included or not. > > So if default build on windows has NLS included, it probably means that > either there's something specific on edb's build (I have no idea how their > build is produced) or their version of msvc is responsible for that. > > [1]: https://www.postgresql.org/docs/current/install-windows- > full.html#id-1.6.4.8.10 --- Hello, So you are seeing a 100x difference. > Execution Time: 44963.747 ms > Execution Time: 460.527 ms I see on https://www.postgresql.org/download/ that there is a different installer from 2ndQuadrant. I am going to try that one and see what I come up with. Are there any other "standard" distros of Postgres that I could try out? Additionally, is there a DLL or EXE file that you could make available to me that I could simply patch on my current install and see if it makes any difference? Or a zip of the lib/bin folders? I found out I could download Visual Studio community edition so I am trying this, but may not have the time to get through a build any time soon as per my unfamiliarity with the process. I'll follow Ranier's steps and see if that gets me somewhere. Thank you, Laurent.
Re: pg_restore schema dump to schema with different name
I agree with that.But, probably its good idea to add this feature as many people are migrating from oracle to postgres. clone/restore schemas to existing cluster for any test cases like sandbox schema, temp schema as live backup schema etc. Thanks,Rj On Tuesday, August 24, 2021, 07:56:20 AM PDT, David G. Johnston wrote: On Mon, Aug 23, 2021 at 2:46 AM Nagaraj Raj wrote: Currently this is not something can do. this functionality is there in oracle. Is this future considering to add? (it would really help for create any test schemas without disturbing current schema. ) I find this to be not all that useful. Current practice is to avoid relying on search_path and, in general, to schema-qualify object references (yes, attaching a local SET search_path to a function works, not sure how it would play out in this context). Performing a dependency and contextual rename of one schema name to another is challenging given all of that, and impossible if the schema name is hard-coded into a function body. I won't say we wouldn't accept such a patch, but as this isn't exactly a new problem or realization, and the feature doesn't presently exist, that for whatever reasons individuals may have no one has chosen to volunteer or fund such development. I don't even remember seeing a proposal in the past 5 or so years. David J.
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
> I see on https://www.postgresql.org/download/ that there is a different > installer from 2ndQuadrant. I am going to try that one and see what I > come up with. Are there any other "standard" distros of Postgres that I > could try out? > > I found out I could download Visual Studio community edition so I am > trying this, but may not have the time to get through a build any time > soon as per my unfamiliarity with the process. I'll follow Ranier's steps > and see if that gets me somewhere. > > Thank you, > Laurent. Hello all, I think I had a breakthrough. I tried to create a local build and wasn't able to. But I downloaded the 2nd Quadrant installer and the issue disappeared!!! I think this is proof that it's not my personal environment, nor something intrinsic in the codebase, but definitely something in the standard EDB installer. create table sampletest (a varchar, b varchar); insert into sampletest (a, b) select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar from generate_series(1,10); CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) RETURNS real AS $$ BEGIN RETURN case when str is null then val else str::real end; EXCEPTION WHEN OTHERS THEN RETURN val; END; $$ LANGUAGE plpgsql COST 1 IMMUTABLE; explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" from sampletest; --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=2092.922..2092.923 rows=1 loops=1) -- Buffers: shared hit=637 -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=15) (actual time=0.028..23.925 rows=10 loops=1) --Buffers: shared hit=637 --Planning Time: 0.168 ms --Execution Time: 2092.957 ms explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as "b" from sampletest; --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=369.475..369.476 rows=1 loops=1) -- Buffers: shared hit=637 -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=8) (actual time=0.020..18.746 rows=10 loops=1) --Buffers: shared hit=637 --Planning Time: 0.129 ms --Execution Time: 369.507 ms Thank you, Laurent!
RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
-Message d'origine- De : [email protected] Envoyé : mardi 31 août 2021 04:18 À : [email protected]; Julien Rouhaud Cc : Tom Lane ; Ranier Vilela ; Andrew Dunstan ; Justin Pryzby ; [email protected] Objet : RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 Importance : Haute > I see on https://www.postgresql.org/download/ that there is a different > installer from 2ndQuadrant. I am going to try that one and see what I > come up with. Are there any other "standard" distros of Postgres that I > could try out? > > I found out I could download Visual Studio community edition so I am > trying this, but may not have the time to get through a build any time > soon as per my unfamiliarity with the process. I'll follow Ranier's steps > and see if that gets me somewhere. > > Thank you, > Laurent. Hello all, I think I had a breakthrough. I tried to create a local build and wasn't able to. But I downloaded the 2nd Quadrant installer and the issue disappeared!!! I think this is proof that it's not my personal environment, nor something intrinsic in the codebase, but definitely something in the standard EDB installer. create table sampletest (a varchar, b varchar); insert into sampletest (a, b) select substr(md5(random()::text), 0, 15), (1*random())::integer::varchar from generate_series(1,10); CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) RETURNS real AS $$ BEGIN RETURN case when str is null then val else str::real end; EXCEPTION WHEN OTHERS THEN RETURN val; END; $$ LANGUAGE plpgsql COST 1 IMMUTABLE; explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(a, null)) as "a" from sampletest; --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=2092.922..2092.923 rows=1 loops=1) -- Buffers: shared hit=637 -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=15) (actual time=0.028..23.925 rows=10 loops=1) --Buffers: shared hit=637 --Planning Time: 0.168 ms --Execution Time: 2092.957 ms explain (analyze,buffers,COSTS,TIMING) select MAX(toFloat(b, null)) as "b" from sampletest; --Aggregate (cost=2137.00..2137.01 rows=1 width=4) (actual time=369.475..369.476 rows=1 loops=1) -- Buffers: shared hit=637 -- -> Seq Scan on sampletest (cost=0.00..1637.00 rows=10 width=8) (actual time=0.020..18.746 rows=10 loops=1) --Buffers: shared hit=637 --Planning Time: 0.129 ms --Execution Time: 369.507 ms Thank you, Laurent! _ Hi, Something which has nothing with the thread but I think it must be said :-) Why substring(x, 0, ...)? msym=> select substr('abcde', 0, 3), substr('abcde', 1, 3); substr | substr + ab | abc Michel SALAIS
