Re: plans for PostgreSQL 12

2018-06-04 8:35 GMT+02:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:

> On 4 June 2018 at 06:08, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>