Логинимся в систему как пользователь postgres:

sudo -i -u postgres

psql - интерактивный терминал для доступа к БД

Запускаем утилиту psql, подключаемся к базе данных "demo":

psql -d demo

Или делаем это одновременно:

sudo -u postgres psql

Выход - \q

Выбор БД - \c <dbname>

Выполнить SQL-файл - psql -f filename.sql -U postgres

• Для создания таблицы служит команда CREATE TABLE

CREATE TABLE имя-таблицы
(
имя-поля тип-данных [ограничения-целостности],
имя-поля тип-данных [ограничения-целостности],
...
имя-поля тип-данных [ограничения-целостности],
[ограничения целостности]],
[первичный ключ],
[внешний ключ]
);

• Просмотр свойств созданной таблицы - команда \d

\d <имя_таблицы

• Удаление таблицы - команда DROP TABLE

DROP TABLE <имя_таблицы>;

• Ввод данных в таблицу - команда INSERT INTO

INSERT INTO <имя_таблицы> [(имя-атрибута, имя-атрибута, ...)]
VALUES (значение-атрибута, значение атрибута, ...);

• Выборка данных - SELECT

SELECT имя-атрибута, имя-атрибута, ... FROM <имя-таблицы>;

или

SELECT * FROM <имя таблицы>;

или

SELECT * FROM <имя таблицы> ORDER BY <имя_столбца> [DESC];

• Выборка с фильтрацией - SELECT ... WHERE

SELECT * FROM <имя таблицы> WHERE <условия>;

Например,

SELECT * FROM test WHERE field1 > 0;

• Обновление/изменение данных в таблице - команда UPDATE

UPDATE <имя таблицы> SET имя-атрибута1 = значение1, имя-атрибута2 = значение2 WHERE <условие>;

• Удаление строк в таблице - команда DELETE

DELETE FROM <Имя-таблицы> WHERE <условие>;

Например,

DELETE FROM test WHERE field1>0 OR field1<100;

• Первичный и внешний ключи - PRIMARY KEY и FOREIGN KEY

CREATE TABLE test
(
field1 char(3) NOT NULL,
field2 varchar(4) NOT NULL,
PRIMARY KEY (field1 [, field2]),
FOREIGN KEY (field1)
 REFERENCES <other_table> (<other_table_field>)
[ON DELETE CASCADE]
);

ON DELETE CASCADE - при удалении какой-либо строки из таблицы other_table - удаление строк из таблицы test берёт на себя СУБД

• GROUP BY

SELECT field1, count(*) FROM <table> GROUP BY field1;

1. Числовые типы

• Целочисленные типы: smallint (int2), integer (int4), bigint (int8)

• Числа фикисированной точности : numeric и decimal** . Имеют scale (масштаб) и precision (точность) - например, numeric(6,2) - 6 знаков, из них 2 - после запятой

• Числа с плавающей точкой : real (1E-37..1E+37, не менее 6 десятичных цифр) и double precision (1E-307..1E+308, не менее 15 десятичных цифр)

Сравнение чисел с плавающей точкой может привести к неожиданным результатам

SELECT 0.1::real * 10 = 1.0::real;

Эти типы данных также поддерживают специальные значения Infinity (бесконечность), -Infinity и NaN.

• Тип Serial, а также bigserial и smallserial (соответствуют bigint и smallint)

2. Символьные (строковые типы)

• character varyuing(n) (varchar) и character(n) (char). n-количество символов, а не байт.

• text

Константы символьных типов заключаются в одинарные кавычки - `

Экранирование - дублирование. Например:

SELECT 'PGDAY''17';

Либо так:

SELECT $$PGDAY'17$$;

Строковые константы в стиле Си:

SELECT E'PGDAY\'17';

3. Типы "дата/время"

• Date

SELECT '2023-02-08'::date;

SELECT 'Feb 08, 2023'::date;

SELECT current_date;

SELECT to_char(current_date, 'dd-mm-yyyy');

• Time

SELECT '21:15'::time;

SELECT '11:15:16 pm'::time;

SELECT current_time;, SELECT current_time::timetz; - timetz

SELECT current_time::time; - time

• Timestamp

SELECT timestamp with time zone '2023-02-08 19:19:19';

SELECT current_timestamp;

• Interval

SELECT '1 year 2 months ago'::interval;

SELECT 'P0001-02-03T04:05:06'::interval;

SELECT ('2023-02-08'::timestamp - '2023-02-06'::timestamp)::interval;

• Функция date_trunc - усечение временных отметок с заданной точностью

SELECT (date_trunc('hour', current_timestamp)); - hour, month, mon, year, etc.

• Функция extract - извлечение из временных отметок отдельных полей

SELECT extract('mon' FROM timestamp '2023-02-08 12:34:56.12345');

4. Логический тип

TRUE, 't', 'true', 'y', 'yes', 'on', '1'

FALSE, 'f', 'false', 'n', 'no', 'off', '0'

5. Массивы

CREATE TABLE pilots
(
  pilot_name text,
  schedule integer[]
);

INSERT INTO pilots
  VALUES ('Ivan', '{1,3,5,6,7}'::integer[]),
('Petr', '{1,2,5,7}'::integer[]),
('Pavel', '{2,5}'::integer[]),
('Boris', '{3,5,6}'::integer[]);

Добавить один день пилоту 'Boris':

UPDATE pilots
SET schedule = schedule || 7
WHERE pilot_name = 'Boris';

Функция array_append, в конец списка :

UPDATE pilots
SET schedule = array_append(schedule,6)
WHERE pilot_name = 'Pavel';

Функция array_prepend, в начало списка :

UPDATE pilots
SET schedule = array_prepend(1, schedule)
WHERE pilot_name = 'Pavel';

А также:

Удалить элемент по значению: array_remove(array, elementValue)

Установить элемент массива по индексу:

SET array_name[index] = value

SET array_name[index1 : index2] = ARRAY[value1, value2]

Получить выборку пилотов, летающих по средам:

select * from pilots
where array_position(schedule,3) IS NOT NULL;

Оператор @> проверяет факт, что левый массив содержит все элементы правого

SELECT * from pilots
where schedule @> '{1, 7 }'::integer[];

Оператор && проверяет, пересекаются ли множества элементов массивов:

# во вторник или пятницу
SELECT * from pilots
where schedule && '{2,5}'::integer[];

unnest - развернуть массив в колонку

SELECT unnest(schedule) as days_of FROM pilots
WHERE pilot_name = 'Ivan';

6. Типы JSON

json и jsonb (рекомендуется)

CREATE TABLE test
(
 name text,
 hobbies jsonb
);

INSERT INTO test VALUES  (
'ivan', '{"sports": ["soccer", "swimming"], "home_lib" : true }'::jsonb);

SELECT * from test
WHERE hobbies @> '{"sports":["soccer"]}'::jsonb;

SELECT name, hobbies->'sports' as sports from test
WHERE hobbies @> '{"sports":["soccer"]}'::jsonb;

# Список, где есть ключ 'sports':
SELECT count(*) from test
WHERE hobbies ? 'sports';

# Обновить hobbies значением только ["hockey"]
update test set hobbies = hobbies || '{"sports": ["hockey"]}' where name = 'ivan';

# Добавить значение с индексом 1 к массиву:
update test set hobbies = jsonb_set(hobbies, '{sports,1}', '"soccer"') where name = 'ivan'; 

1. Значения по умолчанию и ограничения целостности

Значения по умолчанию:

# DEFAULT
CREATE TABLE test
(
field0 text,
 field1 numeric(1) DEFAULT 5
);

Ограничение CHECK

CREATE TABLE test
(
 field1 numeric(1) CHECK (field1 = 1 OR field1 = 2),
 field2 numeric(2) CHECK (field2 >=3)
);

Ограничение уникальности UNIQUE

CREATE TABLE test
(
...
field1 numeric(1) UNIQUE
...
);

Первичный ключ - уникальный идентификатор строк в таблице

CREATE TABLE test
(
...
 field1 numeric(1) PRIMARY KEY,
...
);

# или

CREATE TABLE test
(
...
 field1 numeric(1),
 field2 numeric(1),
...
PRIMARY KEY (field1, field2)
);

Внешние ключи - средство поддержания ссылочной целостности между таблицами

Referencing - ссылающаяся таблица

Referenced - ссылочная таблица

CREATE TABLE table1
(
field0 numeric(5) REFERENCES table2 (field1),
...
);

#или

CREATE TABLE table1
(
field0 numeric(5) REFERENCES table2,
...
);

#или

CREATE TABLE table1
(
field0 numeric(5),
...
FOREIGN KEY (field0)
REFERENCES table2 (field1)
);

ON DELETE CASCADE - удаление связанных строк из таблицы при удалении ключа, на который ссылается таблица

CREATE TABLE test1
(
field0 numeric(5),
...
FOREIGN KEY (field0) REFERENCES test2 (field1)
ON DELETE CASCADE
);

ON DELETE RESTRICT - запрет удаления из ссылочной таблицы, если в ссылающейся таблице есть есть хотя бы одна строка, ссылающаяся на удаляемую строку

ON DELETE NO ACTION - тоже запрет, но проверка выполняется на более поздних этапах транзакции

ON DELETE SET NULL - присваивание атрибутам внешнего ключа в ссылочной таблице значения NULL

ON DELETE SET DEFAULT - присваивание атрибутам внешнего ключа значения по умолчанию.

ON UPDATE CASCADE - новое значение атрибута будет скопировано во все строки, ссылающиеся на обновленную строку.

2. Создание и удаление таблиц

Сначала должны создаваться ссылочные таблицы, а затем - ссылающиеся

\d <table_name - посмотреть описание таблицы, можно указывать только уникальную часть имени таблицы

Комментарии к полям:

CONNECT ON COLUMN <table>.<column> Is 'Comment_value';

Чтобы увидеть комментарии, команда \d+ <table_name>

Удалить таблицу - DROP TABLE <table_name>;

DROP TABLE <table_name> CASCADE; - каскадное удаление зависимых объектов (удаляются не зависимые таблицы, а внешние ключи в них).

3. Модификация таблиц

ALTER TABLE...

...ADD COLUMN - добавить поле

...DROP COLUMN - удалить поле

...ADD CHECK - добавить ограничение

...ADD CONSTRAINT - добавить ограничение

ALTER TABLE test1
   ADD COLUMN field9 integer;
ALTER TABLE test1
   ALTER COLUMN field9 NOT NULL;
ALTER TABLE test1
   ADD CHECK (field9 >= 100)

ALTER TABLE test1 ALTER COLUMN field9 DROP NOT NULL;

ALTER TABLE test1 DROP COLUMN field9;

ALTER TABLE test1 DROP COLUMN field9;

ALTER TABLE test1 ALTER COLUMN field9 SET DATA TYPE numeric(5,2);

ALTER TABLE test1
ALTER COLUMN field1
SET DATA TYPE integer
USING  (CASE
WHEN field1='value1' THEN 1
...
WHEN field1='value10' THEN 10
ELSE 99
END);

ALTER TABLE test1 RENAME COLUMN field9 TO field99;

4. Представления

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

CREATE VIEW <view_name> [(field1_name [, ...] ) ]
AS <query>;

Если представление уже существует, его можно обновить командой

CREATE OR REPLACE VIEW ...

Удалить представление - DROP VIEW

Создать материализованное представление - CREATE MATERIALIZED VIEW <view_name>

Создать материализованное представление, но не заполнять данными - ...WITH NO DATA

Заполнить данными - REFRESH MATERIALIZED VIEW <view_name>

5. Схемы базы данных

Схема - логический фрагмент БД, в котором могут содержаться различные объекты: таблицы, представления, индексы и пр. В БД всегда есть одна схема минимум (Public).

\dn - посмотреть список схем.

Если в БД более одной схемы, то к таблице можно обращаться с именем схемы в пути, например public.testtable

Чтобы не указывать каждый раз имя схемы, можно указать search_path

SHOW search_path;

SET search_path = new_scheme, public ;

1. Дополнительные возможности команды SELECT

LIKE - оператор поиска шаблонов.

% - любая последовательность символов.

_ - в точности один любой символ.

SELECT * FROM table1 WHERE field1 LIKE 'value%';

Можно использовать регулярные выражения

~ - оператор поиска совпадения с учетом регистра символов.

Начало с символа A или B:

`SELECT * FROM table1 WHERE field ~ '^(A|B)';

Инвертирование смысла оператора - !

Поиск с начала строки - ^

Альтернативный выбор - |

Поиск в конце строки - $

Пример: SELECT * FROM table1 WHERE field !~ 'not_ends_at_this$';

Предикаты сравнения - ведут себя также как операторы, но имеют другой синтаксис:

`SELECT * FROM table1 WHERE field1 BETWEEN 100 AND 200;

Вычисляемые столбцы - SELECT (table1.meters / 1000) as kilomteres FROM table1;

ORDER BY - упорядочивание строк в результате.

LIMIT - ограничение вывода

OFFSET - пропуск первых результатов

Условные выражения - CASE WHEN THEN END

CASE
WHEN <condition> THEN <value>
 [
  WHEN <condition2> THEN <value2>
  ...
  ELSE <valueN>
 ]
END

2. Соединения

JOIN - соединение двух таблиц на основе равенства их атрибутов

SELECT * FROM table1 as t1
JOIN table2 as t2
ON t1.field1 = t2.field2
WHERE t1.field2 = 'value1'
ORDER BY t2.field2;

LEFT OUTER JOIN - левое внешнее соединение. Если для строки из левой таблицы не находится ни одной соответствующей строки в правой таблице, тогда в результирующую строку вместо значений столбцов будут помещены значения NULL

RIGHT OUTER JOIN - правое внешнее соединение. В качестве базовой выбирается правая таблица.

FULL OUTER JOIN - полное внешнее соединение. Комбинация внешнего правого и внешнего левого соединений

SELECT * FROM <table1> t1
JOIN <table2> t2
ON <condition_t1_t2>
JOIN <table3> t3
ON <condition t1-t2_t3>
....;

В выражении FROM можно использовать виртуальные таблицы с помощью ключевого слова VALUES

SELECT * FROM <table1> t1
RIGHT OUTER JOIN
( VALUES (0, 1), (0,2), (1,3),....
) as t2 (field1, field2)
ON <condition>;

UNION, INTERSECT, EXCEPT - средства для выполнения операций с выборками как с множествами.

SELECT * FROM <table1>
WHERE <condition1>
UNION / INTERSECT / EXCEPT
SELECT * FROM <table1>
WHERE <condition2>;

3. Агрегирование и группировка

avg - среднее значение. SELECT avg(field1) FROM table1;

max, min, count

Ограничения на выборку - с помощью HAVING

WHERE работает до группировки, HAVING - после

PARTITION BY - задает правило разбиения строк на разделы.

4. Подзапросы

SELECT count(*) FROM table1
WHERE field1 > (SELECT avg(field1) FROM table1);

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

Если подзапрос выдает множество скалярных значений, можно использовать такой подзапрос в IN

Когда от подзапроса требуется лишь установить сам факт наличия или отсутствия строк в таблице - можно использовать EXISTS:

SELECT DISTINCT field1 from table1 t1
WHERE NOT EXISTS 
(
SELECT * FROM table2 t2
 WHERE t1.fieldX = t2.fieldY
...
) AND t1.fieldX <> 'value1';

В теории подзапрос выполняется для каждой для каждой строки из внешнего запроса. Однако на практике это может быть оптимизировано планировщиком.

В сложных запросах могут использоваться вложенные подзапросы

Сложные запросы можно делать более наглядными за счет выделения подзапроса в отдельную конструкцию - общее табличное выражение, Common Table Expression (CTE)

WITH ts AS
(
SELECT ...
...
)
SELECT .... FROM ts
...

Можно также использовать рекурсивные общие табличные выражения:

WITH RECURSIVE rts (min_sum, max_sum) AS
(
 VALUES (0, 10) 
UNION ALL
SELECT min_sum + 10, max_sum + 10
FROM rts
WHERE max_sum < (SELECT max(field1) FROM table1)
)
SELECT * FROM rts

1. Вставка строк в таблицы

INSERT INTO + CTE:

WITH add_row AS
(
INSERT INTO table_temp
SELECT * FROM table
RETURNING *
)
INSERT INTO table_log
SELECT add_row.field1, add_rown.field2, current_timestamp, 'INSERT'
FROM add_row;

RETURNING * - возвращает внешнему запросу все строки

INSERT INTO ... VALUES .... ON CONFLICT [(field1[, ...])] DO NOTHING:

INSERT INTO table_temp
VALUES ('value1', 'value2', 1000)
ON CONFLICT DO NOTHING;

COPY - массовый ввод строк в таблицу

COPY <table_name> FROM './<path_to_txt/filename.txt';

filename.txt:

field1_val1 field2_val1 field3_val1
field1_val2 field2_val2 field3_val2
....
field1_valN field2_valN field3_valN

Экспорт в файл: COPY <table_name> TO './path_to_file/filename.txt' WITH (FORMAT CSV);

2. Обновление строк в таблицах

UPDATE - команда предназначеня для обновления данных.

UPDATE table_name
SET field1 = field1*1.5
WHERE field2 = 'Value2';

3. Удаление строк в таблицах

DELETE FROM table_name
WHERE field1 = 'value1';

Выбирать строки для удаления можно двумя способами - WHERE или USING

DELETE FROM table_name t1
USING table2 t2
WHERE t1.field1 = t2.field1;

1. Общая информация

Строки в таблицах хранятся в неупорядоченном виде. Для ускорения запросов используются индексы. Индекс является упорядоченной структурой, что значительно ускоряет поиск. При выполнении поиска планировщик проверяет, есть ли индекс в таблице для тех же столбцов что и в поиске. Если да - планировщик оценивает целесообразность применения индекса. Если целесообразно - сначала идет поиск в индексе, а потом обращение к таблице с использованием указателей из индекса.

Индексы требуют накладных расходов на их создание и поддержание в актуальном состоянии.

Для PRIMARY KEY, UNIQUE индекс создаётся автоматически.

Тип индекса по умолчанию - btree

Создать индекс:

CREATE INDEX <index_name>
ON <table_name> (column-name, ...);

Удалить индекс:

DROP INDEX <index_name>;

2. Индексы по нескольким столбцам

Индексы полезны, когда из таблицы выбирается лишь небольшая доля строк - высокая селективность выборки.

В случае использования ORDER BY в комбинации с LIMIT явная сортировка (при отсутствии индекса) потребует обработки всех строк таблицы. Но если есть индекс по всем столбцам, которые входят в ORDER BY, то первые несколько строк для LIMIT могут быть извлечены непосредственное, без полного сканирования остальных строк.

3. Уникальные индексы

CREATE UNIQUE INDEX <index_name>
ON <table_name (column_name, ...);

Мы не сможем ввести в таблицу table_name строки с дублированными значениями column_name. Можно было бы создать ограничение уникальности для столбца column_name при создании таблицы - в таком случае уникальный индекс создаётся автоматически.

В уникальных индексах допустим NULL. NULL не считается совпадающим ни с другими значениями ни с другими NULL

4. Индексы на основе выражений

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

CREATE UNIQUE INDEX index_name
ON table_name ( lower(column_name));

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

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

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

CREATE INDEX index_name
ON table_name (column_name)
WHERE column2 > 1000;

1. Общая информация

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

Транзакция переводит БД из одного целостного состояния в другое целостное состояние.

У транзакции может быть два исхода - успешное выполнение или откат (rollback).

Реализация транзакций в PostgreSQL основана на многоверсионной модели (Multiversion Concurrency Control, MVCC). Каждый SQL-оператор видит снапшот данных. При этом параллельные транзакции не нарушают согласованность этого снимка.

Важное следствие применения MVCC - операции чтения никогда не блокируются операциями записи и наоборот.

Транзакции должны обладать следующими свойствами (ACID):

1. Атомарность (atomicity). Либо транзакция будет выполнена полностью, либо не будет выполнена вообще.

2. Согласованность (consistency). В результате выполенения, БД переходит из одного согласованного состояния в другое.

3. Изолированность (isolation). Во время выполнения транзакции на нее не оказывают влияние другие транзакции.

4. Долговечность (durability). После успешной фиксации, пользователь должен быть уверен что данные надежно сохранены и впоследствии могут быть извлечены независимо от последующих сбоев в работе системы.

При параллельном выполнении транзакций возможны следующие феномены:

1. Потерянное обновление (Lost update). Когда разные транзакции одновременно изменяют одни и те же данные, то после фиксации изменений может оказаться что одна транзакция переписала данные другой транзакции.

2. Грязное чтение (dirty read). Транзакция читает данные, измененные другой транзакцией. Если другая транзакция будет отменена - окажется что транзакция прочитала данные, которых больше нет в системе.

3. Неповторяющееся чтение (non-repeatable read). При повторном чтении одних и тех же данных в рамках одной транзакции получается разный результат (другая транзакция могла изменить данные между двумя чтениями).

4. Фантомное чтение (Phantom read). Транзакция повторно выбирает одно и то же множество строк. Но между выборкой другая транзакция могла изменить данные, поэтому во второй раз может быть получено другое множество строк.

5. Аномалия сериализации (serialization anomaly). Результат успешной фиксации группы транзакций, выполняющихся параллельно, не совпадает в результатом ни одного из возможных вариантов кобинаций этих транзакций, выполняющихся последовательно.

Для конкретизации степени независимости параллельных транзакций вводится понятие уровня изоляции транзакций:

1. Read Uncomitted. Самый низкий уровень изоляции. По стандарту SQL (но не в PostgreSQL) на этом уровне допускается чтение грязных данных.

2. Read Committed (по умолчанию). Не допускается чтение грязных данных.

3. Repeatable Read. Не допускается чтение грязных данных + не допускается неповторяющееся чтение. Транзакция, использующая данный уровень изоляции делает снимок данных не перед каждой командой, а перед выполнением первого запроса транзакции . На этом и следующем уровне приложение должно быть готово к откатам в транзакциях.

4. Serializable. Не допускаются в том числе и аномалии сериализации.

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
[ SET TRANSACTION ISOLATION LEVEL READ COMMITTED; ]
....
COMMIT;

COMMIT - завершить транзакцию, ROLLBACK - откатить.

2. Блокировки

SELECT ... FOR UPDATE - позволяет заблокировать отдельные строки в таблице с целью последующего их обновления.

Параллельные транзакции будут приостановлены, пока не произойдет обновление (завершена транзакция где SELECT).

LOCK TABLE <table_name> IN ACCESS EXCLUSIVE MODE; - блокировка на уровне всей таблицы.

1. Основные понятия

Метод доступа - характеризует способ, который используется для просмотра таблиц и тех строк, которые соответствуют критериям отбора:

• Последовательный просмотр (sequential scan) - индекс не используется. Используется, когда селективность выборки низка (нужно выбрать все или почти все строки)

• Просмотр по индексу (index scan) - при низкой селективости выборки (нужно выбрать много строк) не даёт выигрыша в производительности, даже замедляет работу.

• Просмотр исключительно на основе индекса (index only scan) - использует карты видимости, позволяет сократить число операций ввода/вывода. Эффективен, когда выбираемые данные изменяются редко

• Просмотр на основе битовой карты (bitmap scan) - модификация просмотра на основе индекса. Сначала строится битовая карта, в которой указывается, в каких страницах таблицы содержатся требуемые строки.

Способ соединения наборов строк (join). Не путать со способом соединения таблиц

Есть три способа: вложенный цикл (nested loop), хеширование (hash join), слияние (Merge join).

• Вложенный цикл - перебираются все строки из "внешнего" набора и для каждой из них производится поиск соответствующих строк во "внутреннем" наборе. Эффективен для небольших выборок

• Соединение хешированием - строки одного набора помещаются в хеш-таблицу, содержащуюся в памяти. А строки из второго набора перебираются. Метод эффективен для больших выборок

• Соединение методом слияния - аналогично сортировке слиянием. Пригоден для больших наборов строк.

2. План запроса

Прежде чем выполнять запрос, PostgreSQL строит его план. Планированием занимается специальная подсистема - планировщик (planner). Посмотреть план выполнения каждого запроса - команда EXPLAIN

EXPLAIN SELECT * FROM table1;

3. Управление планировщиком

Параметры управления планировщиком можно менять командой SET для текущего сеанса работы.

Запретить использовать метод соединения на основе хеширования:

SET enable_hashjoin = off;

Запретить метод соединения слиянием:

SET enable_mergejoin = off;

Запретить соединения вложенным циклом:

SET enable_nestloop = off;

Вышеперечисленные команды не запрещают методы полностью, а назначают методам высокую стоимость.

В команде EXPLAIN есть опция ANALYZE, которая выводит на экран фактические затраты времени на выполнение запроса и число фактически выбранных строк. То есть выполняется запрос, хотя строки не выводятся на экран.

Фактические затраты выводятся в миллисекундах, а оценки стоимости - в условных единицах, поэтому они могут не совпадать.

4. Оптимизация запросов

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

Повлиять на скорость выполнения можно несколькими способами:

• Обновление статистики, на основе которой планировщик строит планы.

• Изменение исходного кода запроса

• Изменение схемы данных, связанное с денормализацией (создание материализованных представлений и временных таблиц, индексов и тд.)

• Изменение параметров планировщика

Если вы предполагаете, что у планировщика неактуальная статистика, её можно принудительно обновить командой ANALYZE <table name>;

Задаем пароль пользователю postgres:

psql -c "ALTER ROLE postgres PASSWORD 'ВАШ_ПАРОЛЬ'"

Создаём backup:

mkdir ~/pg_backup
sudo chown postgres ~/pg_backup

sudo -i -u postgres

pg_dumpall -c | gzip -c > /home/<user>/pg_backup/pg13_backup.gz

Восстановление из бэкапа:

cd ~/pg_backup
sudo chmod -R 755 ./

sudo -i -u postgres

gunzip -c /home/<user>/pg_backup/pg13_backup.gz > pg13_backup.out

psql -d postgres -f /home/<user>/pg_backup/pg13_backup.out

См. также