Re: Domain check taking place unnecessarily?
On Thu, 9 Feb 2023, Tom Lane wrote: > Mark Hills writes: > > On Wed, 8 Feb 2023, Laurenz Albe wrote: > >> It takes 30 seconds to schan the table and determine that all existing > >> rows satisky the constraint. > > > But there's no existing data (note this is adding column, not constraint) > > > Existing rows are guaranteed to satisfy the domain check, because the > > domain check is guaranteed to be immutable (per [1] in my original mail) > > immutable != "will accept null". > > There could be some more optimizations here, perhaps, but there aren't. Well that's no problem at all. Thanks for the clarification. I mentioned this case to a few people and they were also surprised by the outcome, to the point where we wondered if this might be misbehaving. Hence bringing it up in this forum. We'll go ahead and deal with the pauses in production, as I don't think there's a workaround. Thanks -- Mark
For loop execution times in PostgreSQL 12 vs 15
Hi all, I am running a simple test and am curious to know why a difference in execution times between PostgreSQL 12 vs PostgreSQL 15. I have this function: CREATE function test() returns int language plpgsql as $$ declare v_number bigint; v_multiplier float = 3.14159; loop_cnt bigint; begin for loop_cnt in 1..10 loop v_number := 1000; v_number := v_number * v_multiplier; end loop; return 0; end;$$; I execute this in PostgreSQL 12: [cid:f1c8e1b4-c488-457b-93fb-31617a54a567] PostgreSQL 15: [cid:3db591b7-7913-4705-be81-10a5d4c8989f] It is much faster in 15 than in 12, and while I love the performance improvement. I am curious to know the rationale behind this improvement on PostgreSQL 15. The test result is from PostgreSQL on Windows but I observed the same behavior on Linux OS too. Server Spec: Intel i7-8650U CPU @1.90GHz 2.11GHz RAM 16 GB Windows 11 Enterprise Thanks, Adi
Re: For loop execution times in PostgreSQL 12 vs 15
Hi pá 10. 2. 2023 v 19:53 odesílatel Adithya Kumaranchath < [email protected]> napsal: > Hi all, > > I am running a simple test and am curious to know why a difference in > execution times between PostgreSQL 12 vs PostgreSQL 15. > > *I have this function:* > CREATE function test() returns int language plpgsql as $$ > declare > v_number bigint; > v_multiplier float = 3.14159; > loop_cnt bigint; > begin > > for loop_cnt in 1..10 > loop > v_number := 1000; > v_number := v_number * v_multiplier; > end loop; > > return 0; > > end;$$; > > *I execute this in PostgreSQL 12:* > > > > > *PostgreSQL 15:* > > > It is much faster in 15 than in 12, and while I love the performance > improvement. I am curious to know the rationale behind this improvement on > PostgreSQL 15. > > The test result is from PostgreSQL on Windows but I observed the same > behavior on Linux OS too. > > *Server Spec:* > Intel i7-8650U CPU @1.90GHz 2.11GHz > RAM 16 GB > Windows 11 Enterprise > > Thanks, > Adi > Please, don't send screenshots - we believe you :-) Your code can be little bit faster if you use flag IMMUTABLE There were more patches that reduced the overhead of expression's evaluation in PL/pgSQL. History https://github.com/postgres/postgres/commits/master/src/pl/plpgsql/src/pl_exec.c Some interesting commits https://github.com/postgres/postgres/commit/8f59f6b9c0376173a072e4fb7de1edd6a26e6b52 https://github.com/postgres/postgres/commit/fbc7a716084ebccd2a996cc415187c269ea54b3e https://github.com/postgres/postgres/commit/73b06cf893c9d3bb38c11878a12cc29407e78b6c Originally, PL/pgSQL was designed as glue of SQL and the expression evaluation was not too good. It was significantly slower in expression's evaluation than other interpreters like Perl or Python. But lot of people uses PL/pgSQL for numeric calculations with PostGIS, so speed of expression's evaluation is more important than before, and after all optimizations, although the PL/pgSQL is still slower than generic interprets - still PL/pgSQL should be used mainly like glue of SQL, the difference is significantly less - from 10x times slower to 2 slower. Still there is not any JIT - so the performance is almost good I think. Regards Pavel
Re: For loop execution times in PostgreSQL 12 vs 15
Hi > Please, don't send screenshots - we believe you :-) > > Your code can be little bit faster if you use flag IMMUTABLE > > There were more patches that reduced the overhead of expression's > evaluation in PL/pgSQL. > > History > > https://github.com/postgres/postgres/commits/master/src/pl/plpgsql/src/pl_exec.c > > Some interesting commits > > https://github.com/postgres/postgres/commit/8f59f6b9c0376173a072e4fb7de1edd6a26e6b52 > > https://github.com/postgres/postgres/commit/fbc7a716084ebccd2a996cc415187c269ea54b3e > > https://github.com/postgres/postgres/commit/73b06cf893c9d3bb38c11878a12cc29407e78b6c > > Originally, PL/pgSQL was designed as glue of SQL and the expression > evaluation was not too good. It was significantly slower in expression's > evaluation than other interpreters like Perl or Python. > > But lot of people uses PL/pgSQL for numeric calculations with PostGIS, so > speed of expression's evaluation is more important than before, and after > all optimizations, although the PL/pgSQL is still slower than generic > interprets - still PL/pgSQL should be used mainly like glue of SQL, the > difference is significantly less - from 10x times slower to 2 slower. Still > there is not any JIT - so the performance is almost good I think. > still there is a lot of overhead there - in profiler the overhead of multiplication is less than 1%. But for significant improvements it needs some form of JIT (Postgres has JIT for SQL expressions, but it is not used for PLpgSQL expressions). On second hand, PL/pgSQL is not designed (and usually) not used for extensive numeric calculations like this. But if somebody try to enhance performance, (s)he will be welcome every time (I think so there is some space for 2x better performance - but it requires JIT). Regards Pavel > Regards > > Pavel > >
