Настройки PostgreSQL на Ubuntu Linux

Прилагаю свои настройки, которые позволили мне решить проблему с производительностью.

Хочу поделиться своими настройками, которые позволили мне решить проблему зависаний тяжелых динамических списков и ошибок

Конфигурация сервера СУБД:

Процессор = Intel(R) Xeon(R) Gold 6128 CPU @ 3.40GHz (12 ядер)
Память = 32GB
Диск = 200GB (SSD)
ОС = Ubuntu 16.04.5 LTS x64
СУБД = PostgeSQL 10.5-9.1C

Платформа 8.3.13.1644, размер базы 30GB

Настройки в файле postgresql.conf

max_connections = 1000
ssl = off
row_security = off
shared_buffers = 8GB
temp_buffers = 256MB
work_mem = 1GB
maintenance_work_mem = 2GB
shared_preload_libraries = ‘online_analyze, plantuner’
bgwriter_delay = 20ms
bgwriter_lru_maxpages = 400
bgwriter_lru_multiplier = 4.0
commit_delay = 1000
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
seq_page_cost = 0.1
random_page_cost = 0.1
cpu_operator_cost = 0.00025
effective_cache_size = 16GB
default_statistics_target = 100
from_collapse_limit = 20
join_collapse_limit = 20
autovacuum_max_workers = 6
autovacuum_naptime = 20s
max_locks_per_transaction = 256
online_analyze.threshold = 50
online_analyze.scale_factor = 0.1
online_analyze.enable = on
online_analyze.verbose = off
online_analyze.local_tracking = on
online_analyze.min_interval = 10000
online_analyze.table_type = ‘temporary’
plantuner.fix_empty_table = ‘on’

fsync и synchronous_commit отключать не стал для надежности.

P.S. для настройки параллелизма отредактировал параметры:

max_worker_processes = 12
max_parallel_workers_per_gather = 12
max_parallel_workers = 12

А так же выполнил скрипт в базе pg_set_parallel.sql

SET search_path = public;

BEGIN;

— I/O functions

CREATE OR REPLACE FUNCTION mchartypmod_in(cstring[])
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchartypmod_out(int4)
RETURNS cstring
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_in(cstring)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_out(mchar)
RETURNS cstring
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_send(mchar)
RETURNS bytea
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_recv(internal)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE TYPE mchar (
    INTERNALLENGTH = -1,
    INPUT = mchar_in,
    OUTPUT = mchar_out,
    TYPMOD_IN   = mchartypmod_in,
    TYPMOD_OUT   = mchartypmod_out,
    RECEIVE   = mchar_recv,
    SEND = mchar_send,
    STORAGE = main
);*/

CREATE OR REPLACE FUNCTION mchar(mchar, integer, boolean)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE CAST (mchar as mchar)
WITH FUNCTION mchar(mchar, integer, boolean) as IMPLICIT;*/

CREATE OR REPLACE FUNCTION mvarchar_in(cstring)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_out(mvarchar)
RETURNS cstring
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_send(mvarchar)
RETURNS bytea
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_recv(internal)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE TYPE mvarchar (
    INTERNALLENGTH = -1,
    INPUT = mvarchar_in,
    OUTPUT = mvarchar_out,
    TYPMOD_IN   = mchartypmod_in,
    TYPMOD_OUT   = mchartypmod_out,
    RECEIVE   = mvarchar_recv,
    SEND = mvarchar_send,
    STORAGE = main
);*/

CREATE OR REPLACE FUNCTION mvarchar(mvarchar, integer, boolean)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE CAST (mvarchar as mvarchar)
WITH FUNCTION mvarchar(mvarchar, integer, boolean) as IMPLICIT;*/

—Operations and functions

CREATE OR REPLACE FUNCTION length(mchar)
RETURNS int4
AS ‘$libdir/mchar’, ‘mchar_length’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION upper(mchar)
RETURNS mchar
AS ‘$libdir/mchar’, ‘mchar_upper’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION lower(mchar)
RETURNS mchar
AS ‘$libdir/mchar’, ‘mchar_lower’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_hash(mchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_concat(mchar, mchar)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR || (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =    mchar_concat
);*/

CREATE OR REPLACE FUNCTION mchar_like(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_notlike(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR ~~ (
    LEFTARG     =   mchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mchar_like,
    RESTRICT   =    likesel,
    JOIN       =    likejoinsel,
    NEGATOR       =    ‘!~~’
);

CREATE OPERATOR !~~ (
    LEFTARG     =   mchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mchar_notlike,
    RESTRICT   =    nlikesel,
    JOIN       =    nlikejoinsel,
    NEGATOR       =    ‘~~’
);*/

CREATE OR REPLACE FUNCTION mchar_regexeq(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_regexne(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR ~ (
    LEFTARG     =   mchar,
    RIGHTARG   =    mchar,
    PROCEDURE   =    mchar_regexeq,
    RESTRICT   =    regexeqsel,
    JOIN       =    regexeqjoinsel,
    NEGATOR       =    ‘!~’
);

CREATE OPERATOR !~ (
    LEFTARG     =   mchar,
    RIGHTARG   =    mchar,
    PROCEDURE   =    mchar_regexne,
    RESTRICT   =    regexnesel,
    JOIN       =    regexnejoinsel,
    NEGATOR       =    ‘~’
);*/

CREATE OR REPLACE FUNCTION similar_escape(mchar, mchar)
RETURNS mchar
AS ‘$libdir/mchar’, ‘mchar_similar_escape’
LANGUAGE C IMMUTABLE;

CREATE OR REPLACE FUNCTION length(mvarchar)
RETURNS int4
AS ‘$libdir/mchar’, ‘mvarchar_length’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION upper(mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_upper’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION lower(mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_lower’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_hash(mvarchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_concat(mvarchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR || (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =    mvarchar_concat
);*/

CREATE OR REPLACE FUNCTION mvarchar_like(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION like_escape(mvarchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_like_escape’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_notlike(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR ~~ (
    LEFTARG     =   mvarchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mvarchar_like,
    RESTRICT   =    likesel,
    JOIN       =    likejoinsel,
    NEGATOR       =    ‘!~~’
);

CREATE OPERATOR !~~ (
    LEFTARG     =   mvarchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mvarchar_notlike,
    RESTRICT   =    nlikesel,
    JOIN       =    nlikejoinsel,
    NEGATOR       =    ‘~~’
);*/

CREATE OR REPLACE FUNCTION mvarchar_regexeq(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_regexne(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR ~ (
    LEFTARG     =   mvarchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mvarchar_regexeq,
    RESTRICT   =    regexeqsel,
    JOIN       =    regexeqjoinsel,
    NEGATOR       =    ‘!~’
);

CREATE OPERATOR !~ (
    LEFTARG     =   mvarchar,
    RIGHTARG   =    mvarchar,
    PROCEDURE   =    mvarchar_regexne,
    RESTRICT   =    regexnesel,
    JOIN       =    regexnejoinsel,
    NEGATOR       =    ‘~’
);*/

CREATE OR REPLACE FUNCTION similar_escape(mvarchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_similar_escape’
LANGUAGE C IMMUTABLE;

CREATE OR REPLACE FUNCTION substr (mchar, int4)
RETURNS mchar
AS ‘$libdir/mchar’, ‘mchar_substring_no_len’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION substr (mchar, int4, int4)
RETURNS mchar
AS ‘$libdir/mchar’, ‘mchar_substring’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION substr (mvarchar, int4)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_substring_no_len’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION substr (mvarchar, int4, int4)
RETURNS mvarchar
AS ‘$libdir/mchar’, ‘mvarchar_substring’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

— Comparing
— MCHAR

CREATE OR REPLACE FUNCTION mchar_icase_cmp(mchar, mchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_eq(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_ne(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_lt(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_le(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_gt(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_icase_ge(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR < (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_lt,
    COMMUTATOR   =    ‘>’,
    NEGATOR       =    ‘>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR > (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_gt,
    COMMUTATOR   =    ‘<‘,
    NEGATOR       =    ‘<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR <= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_le,
    COMMUTATOR   =    ‘>=’,
    NEGATOR       =    ‘>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR >= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_ge,
    COMMUTATOR   =    ‘<=’,
    NEGATOR       =    ‘<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR = (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_eq,
    COMMUTATOR   =    ‘=’,
    NEGATOR       =    ‘<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘<‘,
    SORT2        =    ‘<‘,
    HASHES
);

CREATE OPERATOR <> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_icase_ne,
    COMMUTATOR   =    ‘<>’,
    NEGATOR       =    ‘=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

CREATE OR REPLACE FUNCTION mchar_case_cmp(mchar, mchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_eq(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_ne(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_lt(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_le(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_gt(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mchar_case_ge(mchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR &< (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_lt,
    COMMUTATOR   =    ‘&>’,
    NEGATOR       =    ‘&>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_gt,
    COMMUTATOR   =    ‘&<‘,
    NEGATOR       =    ‘&<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &<= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_le,
    COMMUTATOR   =    ‘&>=’,
    NEGATOR       =    ‘&>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &>= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_ge,
    COMMUTATOR   =    ‘&<=’,
    NEGATOR       =    ‘&<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_eq,
    COMMUTATOR   =    ‘&=’,
    NEGATOR       =    ‘&<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘&<‘,
    SORT2        =    ‘&<‘
);

CREATE OPERATOR &<> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mchar_case_ne,
    COMMUTATOR   =    ‘&<>’,
    NEGATOR       =    ‘&=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

—MVARCHAR

CREATE OR REPLACE FUNCTION mvarchar_icase_cmp(mvarchar, mvarchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_eq(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_ne(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_lt(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_le(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_gt(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_icase_ge(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR < (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_lt,
    COMMUTATOR   =    ‘>’,
    NEGATOR       =    ‘>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR > (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_gt,
    COMMUTATOR   =    ‘<‘,
    NEGATOR       =    ‘<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR <= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_le,
    COMMUTATOR   =    ‘>=’,
    NEGATOR       =    ‘>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR >= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_ge,
    COMMUTATOR   =    ‘<=’,
    NEGATOR       =    ‘<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR = (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_eq,
    COMMUTATOR   =    ‘=’,
    NEGATOR       =    ‘<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘<‘,
    SORT2        =    ‘<‘,
    HASHES
);

CREATE OPERATOR <> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_icase_ne,
    COMMUTATOR   =    ‘<>’,
    NEGATOR       =    ‘=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

CREATE OR REPLACE FUNCTION mvarchar_case_cmp(mvarchar, mvarchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_eq(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_ne(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_lt(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_le(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_gt(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mvarchar_case_ge(mvarchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR &< (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_lt,
    COMMUTATOR   =    ‘&>’,
    NEGATOR       =    ‘&>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_gt,
    COMMUTATOR   =    ‘&<‘,
    NEGATOR       =    ‘&<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &<= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_le,
    COMMUTATOR   =    ‘&>=’,
    NEGATOR       =    ‘&>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &>= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_ge,
    COMMUTATOR   =    ‘&<=’,
    NEGATOR       =    ‘&<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_eq,
    COMMUTATOR   =    ‘&=’,
    NEGATOR       =    ‘&<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘&<‘,
    SORT2        =    ‘&<‘
);

CREATE OPERATOR &<> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mvarchar_case_ne,
    COMMUTATOR   =    ‘&<>’,
    NEGATOR       =    ‘&=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

— MCHAR <> MVARCHAR

CREATE OR REPLACE FUNCTION mc_mv_icase_cmp(mchar, mvarchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_eq(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_ne(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_lt(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_le(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_gt(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_icase_ge(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR < (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_lt,
    COMMUTATOR   =    ‘>’,
    NEGATOR       =    ‘>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR > (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_gt,
    COMMUTATOR   =    ‘<‘,
    NEGATOR       =    ‘<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR <= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_le,
    COMMUTATOR   =    ‘>=’,
    NEGATOR       =    ‘>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR >= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_ge,
    COMMUTATOR   =    ‘<=’,
    NEGATOR       =    ‘<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR = (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_eq,
    COMMUTATOR   =    ‘=’,
    NEGATOR       =    ‘<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘<‘,
    SORT2        =    ‘<‘
);

CREATE OPERATOR <> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_icase_ne,
    COMMUTATOR   =    ‘<>’,
    NEGATOR       =    ‘=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

CREATE OR REPLACE FUNCTION mc_mv_case_cmp(mchar, mvarchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_eq(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_ne(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_lt(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_le(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_gt(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mc_mv_case_ge(mchar, mvarchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR &< (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_lt,
    COMMUTATOR   =    ‘&>’,
    NEGATOR       =    ‘&>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_gt,
    COMMUTATOR   =    ‘&<‘,
    NEGATOR       =    ‘&<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &<= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_le,
    COMMUTATOR   =    ‘&>=’,
    NEGATOR       =    ‘&>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &>= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_ge,
    COMMUTATOR   =    ‘&<=’,
    NEGATOR       =    ‘&<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &= (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_eq,
    COMMUTATOR   =    ‘&=’,
    NEGATOR       =    ‘&<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘&<‘,
    SORT2        =    ‘&<‘
);

CREATE OPERATOR &<> (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =   mc_mv_case_ne,
    COMMUTATOR   =    ‘&<>’,
    NEGATOR       =    ‘&=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

— MVARCHAR <> MCHAR

CREATE OR REPLACE FUNCTION mv_mc_icase_cmp(mvarchar, mchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_eq(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_ne(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_lt(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_le(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_gt(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_icase_ge(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR < (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_lt,
    COMMUTATOR   =    ‘>’,
    NEGATOR       =    ‘>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR > (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_gt,
    COMMUTATOR   =    ‘<‘,
    NEGATOR       =    ‘<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR <= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_le,
    COMMUTATOR   =    ‘>=’,
    NEGATOR       =    ‘>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR >= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_ge,
    COMMUTATOR   =    ‘<=’,
    NEGATOR       =    ‘<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR = (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_eq,
    COMMUTATOR   =    ‘=’,
    NEGATOR       =    ‘<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘<‘,
    SORT2        =    ‘<‘
);

CREATE OPERATOR <> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_icase_ne,
    COMMUTATOR   =    ‘<>’,
    NEGATOR       =    ‘=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

CREATE OR REPLACE FUNCTION mv_mc_case_cmp(mvarchar, mchar)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_eq(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_ne(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_lt(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_le(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_gt(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION mv_mc_case_ge(mvarchar, mchar)
RETURNS bool
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR &< (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_lt,
    COMMUTATOR   =    ‘&>’,
    NEGATOR       =    ‘&>=’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_gt,
    COMMUTATOR   =    ‘&<‘,
    NEGATOR       =    ‘&<=’,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &<= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_le,
    COMMUTATOR   =    ‘&>=’,
    NEGATOR       =    ‘&>’,
    RESTRICT   =    scalarltsel,
    JOIN       =    scalarltjoinsel
);

CREATE OPERATOR &>= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_ge,
    COMMUTATOR   =    ‘&<=’,
    NEGATOR       =    ‘&<‘,
    RESTRICT   =    scalargtsel,
    JOIN       =    scalargtjoinsel
);

CREATE OPERATOR &= (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_eq,
    COMMUTATOR   =    ‘&=’,
    NEGATOR       =    ‘&<>’,
    RESTRICT   =    eqsel,
    JOIN       =    eqjoinsel,
    SORT1        =    ‘&<‘,
    SORT2        =    ‘&<‘
);

CREATE OPERATOR &<> (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =   mv_mc_case_ne,
    COMMUTATOR   =    ‘&<>’,
    NEGATOR       =    ‘&=’,
    RESTRICT   =    neqsel,
    JOIN       =    neqjoinsel
);*/

— MCHAR — VARCHAR operations

CREATE OR REPLACE FUNCTION mchar_mvarchar_concat(mchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR || (
    LEFTARG       =   mchar,
    RIGHTARG   =   mvarchar,
    PROCEDURE   =    mchar_mvarchar_concat
);*/

CREATE OR REPLACE FUNCTION mvarchar_mchar_concat(mvarchar, mchar)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE OPERATOR || (
    LEFTARG       =   mvarchar,
    RIGHTARG   =   mchar,
    PROCEDURE   =    mvarchar_mchar_concat
);*/

CREATE OR REPLACE FUNCTION mvarchar_mchar(mvarchar, integer, boolean)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE CAST (mvarchar as mchar)
WITH FUNCTION mvarchar_mchar(mvarchar, integer, boolean) as IMPLICIT;*/

CREATE OR REPLACE FUNCTION mchar_mvarchar(mchar, integer, boolean)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE CAST (mchar as mvarchar)
WITH FUNCTION mchar_mvarchar(mchar, integer, boolean) as IMPLICIT;*/

— Aggregates

CREATE OR REPLACE FUNCTION mchar_larger(mchar, mchar)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE AGGREGATE max (
    BASETYPE   =    mchar,
    SFUNC        =    mchar_larger,
    STYPE       =    mchar,
    SORTOP       =    ‘>’
);*/

CREATE OR REPLACE FUNCTION mchar_smaller(mchar, mchar)
RETURNS mchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE AGGREGATE min (
    BASETYPE   =    mchar,
    SFUNC        =    mchar_smaller,
    STYPE       =    mchar,
    SORTOP       =    ‘<‘
);*/

CREATE OR REPLACE FUNCTION mvarchar_larger(mvarchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE AGGREGATE max (
    BASETYPE   =    mvarchar,
    SFUNC        =    mvarchar_larger,
    STYPE       =    mvarchar,
    SORTOP       =    ‘>’
);*/

CREATE OR REPLACE FUNCTION mvarchar_smaller(mvarchar, mvarchar)
RETURNS mvarchar
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

/*CREATE AGGREGATE min (
    BASETYPE   =    mvarchar,
    SFUNC        =    mvarchar_smaller,
    STYPE       =    mvarchar,
    SORTOP       =    ‘<‘
);*/

— B-tree support
/*CREATE OPERATOR FAMILY icase_ops USING btree;
CREATE OPERATOR FAMILY case_ops USING btree;

CREATE OPERATOR CLASS mchar_icase_ops
DEFAULT FOR TYPE mchar USING btree FAMILY icase_ops AS
        OPERATOR        1       < ,
   OPERATOR        2       <= ,
   OPERATOR        3       = ,
   OPERATOR        4       >= ,
   OPERATOR        5       > ,
   FUNCTION        1       mchar_icase_cmp(mchar, mchar),
        OPERATOR        1       < (mchar, mvarchar),
   OPERATOR        2       <= (mchar, mvarchar),
   OPERATOR        3       = (mchar, mvarchar),
   OPERATOR        4       >= (mchar, mvarchar),
   OPERATOR        5       > (mchar, mvarchar),
   FUNCTION        1       mc_mv_icase_cmp(mchar, mvarchar);

CREATE OPERATOR CLASS mchar_case_ops
FOR TYPE mchar USING btree FAMILY case_ops AS
        OPERATOR        1       &< ,
   OPERATOR        2       &<= ,
   OPERATOR        3       &= ,
   OPERATOR        4       &>= ,
   OPERATOR        5       &> ,
   FUNCTION        1       mchar_case_cmp(mchar, mchar),
        OPERATOR        1       &< (mchar, mvarchar),
   OPERATOR        2       &<= (mchar, mvarchar),
   OPERATOR        3       &= (mchar, mvarchar),
   OPERATOR        4       &>= (mchar, mvarchar),
   OPERATOR        5       &> (mchar, mvarchar),
   FUNCTION        1       mc_mv_case_cmp(mchar, mvarchar);

CREATE OPERATOR CLASS mchar_icase_ops
DEFAULT FOR TYPE mchar USING hash AS
OPERATOR 1 = ,
FUNCTION 1 mchar_hash(mchar);

CREATE OPERATOR CLASS mvarchar_icase_ops
DEFAULT FOR TYPE mvarchar USING btree FAMILY icase_ops AS
        OPERATOR        1       < ,
   OPERATOR        2       <= ,
   OPERATOR        3       = ,
   OPERATOR        4       >= ,
   OPERATOR        5       > ,
   FUNCTION        1       mvarchar_icase_cmp(mvarchar, mvarchar),
        OPERATOR        1       < (mvarchar, mchar),
   OPERATOR        2       <= (mvarchar, mchar),
   OPERATOR        3       = (mvarchar, mchar),
   OPERATOR        4       >= (mvarchar, mchar),
   OPERATOR        5       > (mvarchar, mchar),
   FUNCTION        1       mv_mc_icase_cmp(mvarchar, mchar);

CREATE OPERATOR CLASS mvarchar_case_ops
FOR TYPE mvarchar USING btree FAMILY case_ops AS
        OPERATOR        1       &< ,
   OPERATOR        2       &<= ,
   OPERATOR        3       &= ,
   OPERATOR        4       &>= ,
   OPERATOR        5       &> ,
   FUNCTION        1       mvarchar_case_cmp(mvarchar, mvarchar),
        OPERATOR        1       &< (mvarchar, mchar),
   OPERATOR        2       &<= (mvarchar, mchar),
   OPERATOR        3       &= (mvarchar, mchar),
   OPERATOR        4       &>= (mvarchar, mchar),
   OPERATOR        5       &> (mvarchar, mchar),
   FUNCTION        1       mv_mc_case_cmp(mvarchar, mchar);

CREATE OPERATOR CLASS mvarchar_icase_ops
DEFAULT FOR TYPE mvarchar USING hash AS
OPERATOR 1 = ,
FUNCTION 1 mvarchar_hash(mvarchar);

*/
— Index support for LIKE

/*CREATE OR REPLACE FUNCTION mchar_pattern_fixed_prefix(internal, internal, internal)
RETURNS int4
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT;*/

CREATE OR REPLACE FUNCTION mchar_greaterstring(internal)
RETURNS internal
AS ‘$libdir/mchar’
LANGUAGE C IMMUTABLE RETURNS NULL ON NULL INPUT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION isfulleq_mchar(mchar, mchar)
RETURNS bool AS ‘$libdir/mchar’
LANGUAGE C CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE;

CREATE OR REPLACE FUNCTION fullhash_mchar(mchar)
RETURNS int4 AS ‘$libdir/mchar’
LANGUAGE C CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE;

/*
CREATE OPERATOR == (
    LEFTARG     = mchar,
    RIGHTARG    = mchar,
    PROCEDURE   = isfulleq_mchar,
    COMMUTATOR = ‘==’,
    RESTRICT    = eqsel,
    JOIN        = eqjoinsel,
    HASHES
);

CREATE OPERATOR CLASS mchar_fill_ops
FOR TYPE mchar USING hash AS
OPERATOR 1 ==,
FUNCTION 1 fullhash_mchar(mchar);
*/
CREATE OR REPLACE FUNCTION isfulleq_mvarchar(mvarchar, mvarchar)
RETURNS bool AS ‘$libdir/mchar’
LANGUAGE C CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE;

CREATE OR REPLACE FUNCTION fullhash_mvarchar(mvarchar)
RETURNS int4 AS ‘$libdir/mchar’
LANGUAGE C CALLED ON NULL INPUT IMMUTABLE PARALLEL SAFE;

/*
CREATE OPERATOR == (
    LEFTARG     = mvarchar,
    RIGHTARG    = mvarchar,
    PROCEDURE   = isfulleq_mvarchar,
    COMMUTATOR = ‘==’,
    RESTRICT    = eqsel,
    JOIN        = eqjoinsel,
    HASHES
);

CREATE OPERATOR CLASS mvarchar_fill_ops
FOR TYPE mvarchar USING hash AS
OPERATOR 1 ==,
FUNCTION 1 fullhash_mvarchar(mvarchar);
*/
COMMIT;
SET search_path = public;

UPD в версии PostgeSQL 10.5-24.1C стал нормально отрабатывать параметр join_collapse_limit = 20. Еще рекомендую выставить geqo_effort = 1 — сложные запросы будут работать ~2 раза быстрее

Если у вас SSD диски, а не HDD, то рекомендую установить seq_page_cost = random_page_cost (то есть оценка последовтельного доступа к диску равна произвольному для планировщика) — это очень сильно ускоряет работу + если памяти много, то можно понизить значения до минимальных, например 0.1 чтобы предпочтние отдавалось данным в памяти. Установил в значение 0.1 у обоих параметров. enable_nestloop = off выставил на on

14 Comments

Gilev.Vyacheslav 19.01.2019 at 02:38

настройки

online_analyze.enable = off

enable_nestloop = off

для некоторых нагрузок могут не подойти

Reply ↓
ansh15 19.01.2019 at 03:35

Память = 32GB размер базы 30GB

Сэкономили… Ну да, она же (память) дорогая. SSD дешевле, пусть трудится.

На последних версиях платформы, СУБД и конфигураций(типовых) enable_nestloop = off уже не актуально, разве что в редком случае, когда конфу семилетней давности не удосужились обновить. Или не так уж и редком?

Reply ↓
agerov 20.01.2019 at 22:55

И как оно? Какова разница в производительности? Приведённый конфиг не является даже универсальным и для актуальных версий ПО + железа с SSD.

В общем, данные бы до/после.

Reply ↓
agerov 20.01.2019 at 22:58

И что с памятью, при вполне вменяемом ЦП?

Reply ↓
w.r. 21.01.2019 at 08:15

(1)

online_analyze.enable — включение опции (ON) приводило к ошибке: canceling statement due to lock timeout

enable_nestloop — включение оцпии (ON) приводило к зависаниям динамического списка с 30 тыс записями

Reply ↓
viptextil1 21.01.2019 at 09:45

Эх, еще бы знать, как влияет каждый параметр на производительность. И каким образом оптимизировали систему… А то статью нужно бы переименовать на «по смотрите на мой конфиг.»

Reply ↓
w.r. 21.01.2019 at 11:50

(6) просто не счел нужным перепечатывать документацию. Найти на русском можно например на сайте https://postgrespro.ru/docs/postgresql/10/runtime-config-resource

Reply ↓
Dream_kz 24.01.2019 at 14:56

join_collapse_limit = 1

Тут я бы тоже не был так категоричен, запросы с большим количеством соединений могут при такой настройке выполняться очень долго.

В такой связке патч работал бы лучше, ибо работал бы только для временных таблиц, а не для всех

online_analyze.enable = on

online_analyze.table_type = ‘temporary’ //вместо ‘all’

И да, настройки бы «до».

Reply ↓
w.r. 25.01.2019 at 08:47
(8)

протестировал тяжелый запрос — выборка из журнала бухгалтерии с подзапросом, соединением с другими регистрами и объединением. Результаты:
```
join_collapse_limit = 20 // по-умолчанию
online_analyze.enable = on
online_analyze.table_type = ‘temporary’
```
269,251 сек
```
join_collapse_limit = 1
online_analyze.enable = off
online_analyze.table_type = ‘all’
```
193,046 сек

Как я понял, Postgre вообще очень интересный продукт в плане настроек — максимальной производительности можно добиться только экспериментально и не всегда настройка параметров по их описанию, т.е. «как должно работать» = «как работает в реальности»

И еще рекомендую использовать подзапросы в Postgre вместо временных таблиц, иногда выигрыш в скорости выполнения 2 раза

Reply ↓
Dream_kz 25.01.2019 at 08:55

(9)

выборка из журнала бухгалтерии с подзапросом, соединением с другими регистрами и объединением

При таком запросе патч работать не должен, ибо статистика собирается для временных таблиц, которых в запросе нет.

(9)

join_collapse_limit = 20 // по-умолчанию

По умолчанию должно быть 8, как и from_collapse_limit

(9)

И еще рекомендую использовать подзапросы в Postgre вместо временных таблиц, иногда выигрыш в скорости выполнения 2 раза

Опять же из-за отсутствия статистики для временных таблиц

Reply ↓
w.r. 25.01.2019 at 09:28

(10)

При таком запросе патч работать не должен, ибо статистика собирается для временных таблиц, которых в запросе нет.

Тогда непонятно, откуда проседение производительности, если в моем запросе патч и так работать не должен

По умолчанию должно быть 8, как и from_collapse_limit

После этих настроект запрос выполнялся 10 минут, после чего прибил сеанс

Reply ↓
ansh15 25.01.2019 at 10:12

По умолчанию в PostgreSQL, версия 9.6.7-1.1C — join_collapse_limit и from_collapse_limit равно 8.

В PostgreSQL, версия 10.5-10.1C эти параметры равны 20 по умолчанию.

Видимо, в 1С решили, что для большинства последних версий типовых с учетом версии платформы(8.3.13) значение 20 является более оптимальным. Наверное. неоднократно тестировали, прежде чем прийти к такому значению…

Reply ↓
w.r. 14.03.2019 at 12:59

(2)

иповых) enable_nestloop = off уже не актуально, разве что в редком случае, когда конфу семилетней давности не удосужились обновить. Или не так уж и редком?

В современных конфигурациях планировщик заточен под HDD (seq_page_cost = 1,0 и random_page_cost = 4.0), а не для SSD + для машин с маленьким объемом ОЗУ (большие значения оценки доступа к диску по сравнению к данным в памяти). Причем это во всех современных сборках — 1С, Postgres Pro и другие

Пока я не прочитал эту статью и не настроил параметры оценки доступа к диску, мне приходилось ставить enable_nestloop = off, иначе динамические списки в моей концигурации с 30 тыс записями при поиске в них через Ctrl + F висли намертво

Reply ↓
w.r. 14.03.2019 at 13:03

(1)

Собственно пора бы уже фирмам, делающим сборки, да и сообществу postgre ставить настройки, которые соотвествуют железу современных серверов (для планировщика seq_page_cost = 0.1 и random_page_cost = 0.1), а не считать, что на серверах установлен HDD и маленький объеем ОЗУ (по-умолчанию seq_page_cost = 1.0 и random_page_cost = 4.0). Тогда и не придется людям извращаться с enable_nestloop = off

Reply ↓

Настройки PostgreSQL на Ubuntu Linux

Related Posts

14 Comments

Leave a Comment Отменить ответ