Postgres

в постгрес форейгн кей не создает автоматом индекс

-- индекс для текстового поля с поиском через лайк
CREATE INDEX idx_movie_title ON movie (lower(title) text_pattern_ops)

-- like('abcd%') будет поиск в индексе
-- like('%abcd%') НЕ будет поиск в индексе

btree индекс

можно

  • поиск по полному значению
  • поиск по самому левому префиксу
  • поиск по префиксу столбца
  • поиск по диапазону значений
  • поиск по полному совпадению одной части и диапазону в другой части

запросы только по индексу

нельзя

  • поиск без использования левой части ключа
  • нельзя пропускать столбцы
  • оптимизация после поиска в диапазоне

hash - индекс

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

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

gist индекс

gin индекс, инвертированный индекс

  • для полнотекстового поиска
  • для жсон
  • для массива

битовый индекс

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

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

кластерный индекс

покрывающий индекс

содержит все данные для запроса

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;