Схема данных готова для демонстрации приложения.
Создадим базу данных и подключимся к ней:
student$ psql
=> CREATE DATABASE bstore_schema;
CREATE DATABASE
=> \c bstore_schema
You are now connected to database "bstore_schema" as user "student".
Пример создания таблицы с разными типами столбцов и ограничениями целостности.
=> CREATE TABLE people( id serial PRIMARY KEY, name varchar(100) NOT NULL, employee boolean DEFAULT true, CONSTRAINT name_not_empty CHECK( length(name) > 0 ) );
CREATE TABLE
Синтаксис соответствуют стандарту.
Можно использовать различные типы данных (в примере - serial, varchar, boolean), указывать ограничения целостности на уровне строки (здесь - PRIMARY KEY, NOT NULL) и таблицы (CHECK).
Можно указывать значения по умолчанию, которые будут использованы, если при вставке строк не указать значение столбца.
Вставим пару строк:
=> INSERT INTO people(name) VALUES ('Иванов'),('Петров');
INSERT 0 2
И проверим, что находится в таблице:
=> SELECT * FROM people;
id | name | employee ----+--------+---------- 1 | Иванов | t 2 | Петров | t (2 rows)
Как видно, в поле id попали автоматически сгенерированные номера, а поле employee получило значение по умолчанию.
Если проверить описание таблицы в системном каталоге, мы увидим, во что превратился псевдотип serial:
=> \d people
Table "public.people"
Column | Type | Modifiers
----------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('people_id_seq'::regclass)
name | character varying(100) | not null
employee | boolean | default true
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Check constraints:
"name_not_empty" CHECK (length(name::text) > 0)
Это обычный тип integer со значением по умолчанию из последовательности people_id_seq, которая была создана автоматически.
К последовательности можно при необходимости обращаться и непосредственно:
=> SELECT nextval('people_id_seq');
nextval
---------
3
(1 row)
Последовательность - самый эффективный способ генерации уникальных номеров. Но следует иметь в виду, что последовательность не гарантирует:
Посмотрим еще раз описание таблицы:
=> \d people
Table "public.people"
Column | Type | Modifiers
----------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('people_id_seq'::regclass)
name | character varying(100) | not null
employee | boolean | default true
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Check constraints:
"name_not_empty" CHECK (length(name::text) > 0)
Ограничение PRIMARY KEY упоминается вместо со словами "индекс" и "btree".
Btree (B-дерево) - основной тип индекса, используемый в базах данных для ускорения поиска и для поддержки ограничений целостности (первичного ключа и уникальности).
Представим себе, что в таблице содержится много данных - миллион строк:
=> INSERT INTO people(name, employee) SELECT 'Сидоров', false FROM generate_series(1,1000000) AS s(id);
INSERT 0 1000000
Сколько времени занимает поиск одного значения в такой таблице?
=> \timing on
Timing is on.
=> SELECT * FROM people WHERE name = 'Иванов';
id | name | employee ----+--------+---------- 1 | Иванов | t (1 row) Time: 138,680 ms
=> \timing off
Timing is off.
Если попросить оптимизатор показать план запроса, мы увидим в нем Seq Scan - последовательное сканирование всей таблицы в поисках нужного значения (Filter):
=> EXPLAIN (COSTS OFF) SELECT * FROM people WHERE name = 'Иванов';
QUERY PLAN
-------------------------------------------
Seq Scan on people
Filter: ((name)::text = 'Иванов'::text)
(2 rows)
А если искать по полю, которое проиндексировано?
=> \timing on
Timing is on.
=> SELECT * FROM people WHERE id = 1;
id | name | employee ----+--------+---------- 1 | Иванов | t (1 row) Time: 0,347 ms
=> \timing off
Timing is off.
Невооруженным взглядом видно, что время уменьшилось.
А в плане запроса появился индекс:
=> EXPLAIN (COSTS OFF) SELECT * FROM people WHERE id = 1;
QUERY PLAN
----------------------------------------
Index Scan using people_pkey on people
Index Cond: (id = 1)
(2 rows)
Можно создать индекс и по имени (и проанализировать таблицу, чтобы собрать актуальную статистику):
=> CREATE INDEX ON people(name);
CREATE INDEX
=> ANALYZE people;
ANALYZE
=> EXPLAIN (COSTS OFF) SELECT * FROM people WHERE name = 'Иванов';
QUERY PLAN
-----------------------------------------------
Index Scan using people_name_idx on people
Index Cond: ((name)::text = 'Иванов'::text)
(2 rows)
Однако индекс не является универсальным средством увеличения производительности. Обычно индекс очень полезен, если из таблицы требуется выбрать небольшую долю всех имеющихся строк. Если нужно прочитать много данных, индекс будет только мешать, и оптимизатор это понимает:
=> EXPLAIN (COSTS OFF) SELECT * FROM people WHERE name = 'Сидоров';
QUERY PLAN
--------------------------------------------
Seq Scan on people
Filter: ((name)::text = 'Сидоров'::text)
(2 rows)
Кроме того, надо учитывать накладные расходы на обновление индексов при изменении таблицы и занимаемое ими место на диске.
Создадим представление для отображения только работников:
=> CREATE VIEW employees AS SELECT id, name FROM people WHERE employee;
CREATE VIEW
Теперь имя представления можно использовать практически так же, как и таблицу:
=> SELECT * FROM employees;
id | name ----+-------- 1 | Иванов 2 | Петров (2 rows)
В простом случае с представлением будут работать и другие операции, например:
=> UPDATE employees SET name = initcap(name);
UPDATE 2
С помощью триггеров можно сделать так, чтобы и в сложных случаях для представлений работали вставка, обновление и удаление строк.
При планировании запроса представление "разворачивается" до базовых таблиц:
=> EXPLAIN (COSTS OFF) SELECT * FROM employees;
QUERY PLAN
--------------------
Seq Scan on people
Filter: employee
(2 rows)
Конец демонстрации.