2018-06-04 8:35 GMT+02:00 Simon Riggs <si...@2ndquadrant.com>: > On 4 June 2018 at 06:08, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 4. optimization expression without necessity to create snapshots - > > experiments > > > > @4 There are lot of not database expressions in PLpgSQL - like var1 := > var1 > > + var2 or var1 := var1 + konst. Own calculation needs about 1% of time of > > total expression evaluation time. Almost all time get preparing plan > cache, > > preparing snapshot, .. For this case, when no database object is used, we > > don't need use this infrastructure. I would to measure performance > impact, > > and testing if these optimizations are interesting or not. > > Sounds good. I think this would need to be restricted by operator and > datatype, since in general you won't know if the datatype functions > need a snapshot or not. Immutable functions for the operators ought to > do it, but I think that might not be enough. >
It requires introduction of new "safe" functions (& operators). Immutable functions are not enough safe. CREATE OR REPLACE FUNCTION public.fx() RETURNS integer LANGUAGE plpgsql IMMUTABLE AS $function$ BEGIN RETURN (SELECT count(*) FROM pg_class); END; $function$ postgres=# SELECT fx(); ┌─────┐ │ fx │ ╞═════╡ │ 343 │ └─────┘ (1 row) I have not a name for this new class - maybe "pure immutable". The name is not important in this moment, and I am sure, so native speakers can find good name if it is necessary. For start I would to accept as safe functions only buildin immutable functions (& operators). From practical view it can be good enough for some first public iterations too. I am looking to some big real project (migrated from Oracle) The majority are 1. constant expressions: 0, NULL, '', true, false, 'yes', 'no' 2. simply operators: <, >, <> = again numbers and strings (sometimes logical predicate and, or are used) 3. + and || operators: var := var + varx 4. IS NULL, IS NOT NULL The constant expressions can have special optimization - it is really often pattern if expr then a := const; else a := other_const; end if; Because expressions are not typical bottleneck in PL/pgSQL, then typical speedup will not be 100%, but The PL/pgSQL can lost bad reputation about calculation power, and it can be interesting for PostGIS people and some speedup can be measurable in applications migrated from Oracle - these people did not write code for PL/pgSQL and didn't count number of expressions. And any speedup is not bad. I have not idea, how the code will be ugly - it is a experiment, and I am thinking so it can be very interesting Regards Pavel > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
