Postgres¶
в постгрес форейгн кей не создает автоматом индекс
CREATE [UNIQUE] INDEX [CONCURRENTLY]
[[IF NOT EXISTS] имя] ON имя_таблицы [USING метод]
(
{имя_столбца | (выражение)}
[COLLATE правило_сортировки][класс_операторов]
[ASC|DESC][NULLS {FIRST|LAST}]
[, ...]
)
[WITH (параметр_хранения=значение [, ...])]
[TABLESPACE табл_пространство]
[WHERE предикат]
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
-- класс_операторов
-- -text_pattern_ops
-- -varchar_pattern_ops
-- -bpchar_pattern_ops
-- индекс для текстового поля с поиском через лайк
CREATE INDEX idx_movie_title ON movie (lower(title) text_pattern_ops)
-- like('abcd%') будет поиск в индексе
-- like('%abcd%') НЕ будет поиск в индексе
btree индекс
можно
поиск по полному значению
поиск по самому левому префиксу
поиск по префиксу столбца
поиск по диапазону значений
поиск по полному совпадению одной части и диапазону в другой части
запросы только по индексу
нельзя
поиск без использования левой части ключа
нельзя пропускать столбцы
оптимизация после поиска в диапазоне
CREATE TABLE people (
last name TEXT NOT NULL
, first name TEXT NOT NULL
, date_of_birth TIMESTAMP NOT NULL
, gender INT NOT NULL
);
CREATE INDEX idx_people_name
ON
people USING btree
(lst_name, first_name, date_of_birth;
hash - индекс
данный индекс необходимо использовать только для временных таблиц
нельзя использовать данные в индексе, чтобы избежать чтения строк
нельзя использовать для сортировки
нет поиска по частичному ключю
поддерживают сравнение только на равенство
операции обслуживания индекса могут быть медленными, если количесвто коллизи в индекса большое
хеш индекс не записывается в wal-лог, он не транзакционен
CREATE TEMPORARY TABLE testhash (
fname TEXT NOT NULL
, lname TEXT NOT NULL
);
CREATE INDEX idx_testhash_fname
ON
testhash USING hash
(fname);
gist индекс
CREATE TABLE city (
id SERIAL PRIMARY KEY
, name TEXT NOT NULL
, area polygon
);
CREATE INDEX idx_city_area
ON
city USING gist
(area);
gin индекс, инвертированный индекс
для полнотекстового поиска
для жсон
для массива
битовый индекс
частичный индекс
CREATE INDEX idx_items_avatar_id
ON
items (avatar_id)
WHERE
avatar_id IS NOT NULL;
функциональный индекс
CREATE INDEX idx_movies_title
ON movies
(LOWER(title));
кластерный индекс
CREATE TABLE movies (
id SERIAL PRIMARY KEY
, title TEXT NOT NULL
);
-- кластеризация для таблицы
CLUSTER movies USING movies_pkey;
-- повтроное кластеризация для таблицы
CLUSTER movies;
-- повторное кластеризация всей бд
CLUSTER;
покрывающий индекс
содержит все данные для запроса
explain
обратить внимание - операции с cost - seq sqan - eows removed by filter - sort для больших данных дорогая операция
без параметров, explain просто строит план запроса .. code-block:: sql
explain [(param [, …])] оператор explain [analyze] [verbose] оператор
—param analyze [boolean] - выполнить запрос verbose [boolean] - больше инфы costs [boolean] - стоимость операции buffers [boolean] - буферы, кеши timing [boolean] - стоимость операции foramt {text | xml | json | yaml} - формат текста
-- для дебага
raise notice '%', var;
select * from (values (true), (false))
Создание пользователя базы данных
CREATE ROLE role_name LOGIN PASSWORD '12'
CREATEDB CREATEROLE VALID UNTIL 'infinity';
Создание базы
CREATE DATABASE
db_name
WITH
ENCODING='utf-8'
OWNER=role_name
CONNECTION LIMIT =-1;
Создание последовательности и таблицы
CREATE SEQUENCE
public.person_id_seq;
CREATE TABLE public.table_name (
id integer NOT NULL DEFAULT nextval('person_id_seq'::regclass)
);
ALTER TABLE public.table_name OWNER TO role_name;