Re: plans for PostgreSQL 12
| От | Pavel Stehule |
|---|---|
| Тема | Re: plans for PostgreSQL 12 |
| Дата | |
| Msg-id | CAFj8pRC5x+GYAni4+G1Y6Box2kqWU=64V4NsNHYBG9UB26XF-A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: plans for PostgreSQL 12 (Simon Riggs <simon@2ndquadrant.com>) |
| Ответы |
Re: plans for PostgreSQL 12
(Heikki Linnakangas <hlinnaka@iki.fi>)
|
| Список | pgsql-hackers |
2018-06-04 8:35 GMT+02:00 Simon Riggs <simon@2ndquadrant.com>:
On 4 June 2018 at 06:08, Pavel Stehule <pavel.stehule@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$
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $function$
BEGIN
RETURN (SELECT count(*) FROM pg_class);
END;
$function$
postgres=# SELECT fx();
┌─────┐
│ fx │
╞═════╡
│ 343 │
└─────┘
(1 row)
┌─────┐
│ 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
В списке pgsql-hackers по дате отправления: