Em dom., 29 de ago. de 2021 às 10:35, Tom Lane <[email protected]> escreveu:
> Ranier Vilela <[email protected]> writes: > > I retested this case with HEAD, and it seems to me that NLS does affect > it. > > Sure, there's no question that message translation will have *some* cost. > But on my machine it is an incremental tens-of-percent kind of cost, > and that is the result you're getting as well. So it's not very clear > where these factor-of-several-hundred differences are coming from. > A hypothesis that has not yet come up, may be some defect in the code generation, by the previous msvc compiler used, because in all my tests I always use the latest version, which has several corrections in the code generation part. View this test with one of the attempts to reproduce the problem. msvc: 19.29.30133 para x64 windows 10 64 bits Postgres: 12.8 postgres=# select version(); version ------------------------------------------------------------ PostgreSQL 12.8, compiled by Visual C++ build 1929, 64-bit (1 row) postgres=# drop table sampletest; DROP TABLE postgres=# create table sampletest (a varchar, b varchar); CREATE TABLE postgres=# insert into sampletest (a, b) postgres-# select substr(md5(random()::text), 0, 15), (100000000*random())::integer::varchar postgres-# from generate_series(1,100000); INSERT 0 100000 postgres=# postgres=# CREATE OR REPLACE FUNCTION toFloat(str varchar, val real) postgres-# RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN case when str is null then val else str::real end; postgres$# EXCEPTION WHEN OTHERS THEN postgres$# RETURN val; postgres$# END; postgres$# $$ LANGUAGE plpgsql COST 1 IMMUTABLE; CREATE FUNCTION postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(b, null)) as "b" from sampletest; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=339.978..339.979 rows=1 loops=1) Buffers: shared hit=644 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.032..18.132 rows=100000 loops=1) Buffers: shared hit=637 Planning Time: 3.631 ms Execution Time: 340.330 ms (6 rows) postgres=# explain (analyze,buffers,COSTS,TIMING) postgres-# select MAX(toFloat(a, null)) as "a" from sampletest; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=1477.84..1477.85 rows=1 width=4) (actual time=1724.902..1724.903 rows=1 loops=1) Buffers: shared hit=640 -> Seq Scan on sampletest (cost=0.00..1197.56 rows=56056 width=32) (actual time=0.021..23.489 rows=100000 loops=1) Buffers: shared hit=637 Planning Time: 0.150 ms Execution Time: 1724.930 ms (6 rows) regards, Ranier Vilela
