Информация о занятии

Цели занятия

после занятия вы сможете: заглянуть под капот СУБД.

Краткое содержание

• «перестанем бояться чудодейственной магии PostgreSQL и детально разберём как база данных работает “под капотом»»;

• буферный кеш;

• журнал упреждающей записи;

• контрольная точка;

• страницы и версии строк;

• LRU, снимки и блокировки, а также Vacuum;

• при помощи EXPLAIN рассмотрим как PostreSQL выполняет запрос и попытаемся оптимизировать его выполнение.

Результаты

• оптимизировать запросы и понимать принципы работы СУБД.

Преподаватель

• Василий Никонов

Дата и время

• 14 марта, понедельник в 20:00

• Длительность занятия: 90 минут

Тезисы из вебинара

Постраничное хранение данных

TOAST (The Oversized-Attribute Storage Technique)

Кеш, прогрев

Анализ запросов (explain)

• explain <query> — анализ запросы без его запуска

• explain analyse <query> — реальный запуск

Индексы

Запросики для экспериментов с индексами:

Work.sql

Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

select random_string(10);

create table test(
	"tID" serial, 
	"tInt" integer,
	"t30" varchar(30),
	"tText" text,
	"tBool50" boolean,
	"tBool1" boolean,
	primary key ("tID")
);

insert into test("tInt","t30","tText","tBool50","tBool1")
  select 
  	gs.id,
  	random_string((1 + random()*29)::integer), 
   	random_string((1 + random()*500)::integer),  	
   	random() < 0.5,
  	random() < 0.01
  from generate_series(1,10000) as gs(id);
  
explain select * from test;  
explain select count(*) from test;

explain select count(*) from test where "tInt" = 20;
explain select count(*) from test where "tInt" between 10 and 20;
explain select "tInt" from test where "tInt" between 10 and 20;
create index i_int on test using btree ("tInt");
explain select count(*) from test where "tInt" = 20;
explain select count(*) from test where "tInt" between 10 and 20;
explain select "tInt" from test where "tInt" between 10 and 20;

select count(*) from test;
explain delete from test where "tInt" between 10 and 200;
select count(*) from test;
explain analyse delete from test where "tInt" between 10 and 200;
select count(*) from test;

explain select count(*) from test where length(t30) = 15;
create index i_length on test using btree (length(t30));
explain select * from test where length(t30) = 15;
explain select count(*) from test where length(t30) = 15;

create index i_50 on test using btree ("tBool50");
create index i_01 on test using btree ("tBool1") where "tBool1";

explain select * from test where "tBool50";
explain select * from test where not "tBool50";

explain select * from test where "tBool1";
explain select count(*) from test where "tBool1";
explain select count(*) from test where not "tBool1";


SELECT nspname || '.' || relname  as name,
  pg_size_pretty(pg_total_relation_size(C.oid)) as totalsize, 
  pg_size_pretty(pg_relation_size(C.oid)) as relsize 
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 15;


SELECT 
(1.0*blks_hit/NULLIF(blks_hit+blks_read, 0))::numeric(6,5) AS "доля попаданий cache" 
FROM pg_stat_database where datname = current_database();

SELECT name, setting, unit FROM pg_settings WHERE name in ('shared_buffers','work_mem');

seq_page_cost
random_page_cost

set random_page_cost = 4;

Разбор этих SQL:

Создаем табличку и индексы по ней

Запрос с ипользованием индекса:

еще пример с применением индекса:

Читаем данные не только из индекса (из-за “*“)

Функциональные индексы

Пример: выборка данных из поля заданной длины :

Частичные индексы

• Индексируют указанный диапазон данных (не все):

Здесь в примере индекс i_50 не используется, т.к. не эффективен

Индекс i_01 напротив, эффективен,т.к. ищет только положительные значения (tBol1=true).

Analyse & Vacuum

Ручной и автоматический запуск

Триггеры

Виды триггеров:

“Instead of” — для view

Пример: trigger.sql

CREATE TABLE "tEvents" (
    "tE_ID" serial NOT NULL,
    "tE_Type" varchar(10) NOT NULL,
    "tE_Text" text NOT NULL,
	CONSTRAINT "tE_ID" PRIMARY KEY ("tE_ID")
);

CREATE TABLE "tEventsHistory" (
    "tEH_ID" serial NOT NULL,
    "tEH_Timestamp" timestamp NOT NULL,
    "tEH_Operation" char(1) NOT NULL,
    "tEH_User" text NOT NULL,
    "tE_ID" integer,
    "tE_Type" varchar(10),
    "tE_Text" text,
	CONSTRAINT "tEH_PK" PRIMARY KEY ("tEH_ID")
);


CREATE OR REPLACE FUNCTION "fEventsHistory"()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO "tEventsHistory" SELECT nextval('"tEventsHistory_tEH_ID_seq"'::regclass), now(), 'D', user, OLD.*;
            RETURN OLD;
        ELSIF ((TG_OP = 'UPDATE')AND(OLD.* IS DISTINCT FROM NEW.*)) THEN
            INSERT INTO "tEventsHistory" SELECT nextval('"tEventsHistory_tEH_ID_seq"'::regclass), now(), 'U', user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO "tEventsHistory" SELECT nextval('"tEventsHistory_tEH_ID_seq"'::regclass), now(), 'I', user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL;
    END;
$function$
;

CREATE TRIGGER "trEventsHistory"
AFTER INSERT OR UPDATE OR DELETE ON "tEvents"
    FOR EACH ROW EXECUTE PROCEDURE "fEventsHistory"();
	
insert into "tEvents" ("tE_Type", "tE_Text") values ('1', '1'), ('2','22'), ('3','333');

select * from "tEvents";

select * from "tEventsHistory";

update "tEvents" set "tE_Text" = '22' where length("tE_Text") >= 1; 

select * from "tEventsHistory";

Секционирование и партиционирование

Встроенное партиционирование (вместо триггеров) появилось в 10й и развилось в 11й версии PG

Шардирование — распределение по серверам (будет обсуждаться позже)

Пример: partitions.sql

CREATE TABLE "tData" (
    "tD_ID" bigserial NOT NULL,
    "tD_Source" int2 NOT NULL,
    "tD_Type" int2 NOT NULL,
    "tD_Info" text NOT NULL,
    CONSTRAINT tmp_pk PRIMARY KEY ("tD_ID", "tD_Source")
)
PARTITION BY LIST ("tD_Source");

CREATE TABLE "tDataDefault" PARTITION OF "tData" DEFAULT;

CREATE TABLE "tData0" PARTITION OF "tData" FOR VALUES IN (0);

CREATE TABLE "tData88" PARTITION OF "tData" FOR VALUES IN (88);

insert into "tData" ("tD_Source", "tD_Type", "tD_Info") values (0, 123, 'всё в 0');
insert into "tData" ("tD_Source", "tD_Type", "tD_Info") values (88, 88, 'всё по 88');
insert into "tData" ("tD_Source", "tD_Type", "tD_Info") values (8, 8, 'всё по 8');
insert into "tData" ("tD_Source", "tD_Type", "tD_Info") values (7, 77, 'всё дёшево');

select * from "tData";

explain select * from "tData";

explain select * from "tData" where "tD_Source" = 15;

explain select * from "tData" where "tD_Source" in(0, 88);
explain select * from "tData0" where "tD_Source" in(0, 88);

Дополнительные ссылки

• “Explain anlyse” online editor: https://explain.tensor.ru/ , https://explain.depesz.com/

• Index Maintenance https://wiki.postgresql.org/wiki/Index_Maintenance