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

Ответ 1

Этот вопрос делает ошибочное предположение о том, что первичный ключ вообще налагает порядок таблиц. Это не так. Таблицы PostgreSQL не имеют определенного порядка, с или без первичного ключа; они представляют собой "кучу" строк, расположенных в блоках страниц. Заказ выполняется с помощью предложения ORDER BY запросов при желании.

Возможно, вы думаете, что таблицы PostgreSQL хранятся в виде индексированных таблиц, которые хранятся на диске в порядке первичного ключа, но это не так, как работает Pg. Я думаю, что InnoDB хранит таблицы, организованные первичным ключом (но не проверенные), и он необязателен в некоторых базах данных других поставщиков, используя функцию, часто называемую "кластеризованные индексы" или "индексированные таблицы". Эта функция в настоящее время не поддерживается PostgreSQL (по крайней мере, на 9.3).

Тем не менее, PRIMARY KEY реализуется с использованием индекса UNIQUE, и есть порядок для этого индекса. Он сортируется в порядке возрастания слева и слева от индекса (и, следовательно, первичного ключа), как если бы он был ORDER BY col1 ASC, col2 ASC, col3 ASC;. То же самое относится к любому другому индексу b-tree (в отличие от GiST или GIN) в PostgreSQL, поскольку они реализованы с использованием b + trees.

Итак, в таблице:

CREATE TABLE demo (
   a integer,
   b text, 
   PRIMARY KEY(a,b)
);

система автоматически создаст эквивалент:

CREATE UNIQUE INDEX demo_pkey ON demo(a ASC, b ASC);

Об этом сообщается вам при создании таблицы, например:

regress=>     CREATE TABLE demo (
regress(>        a integer,
regress(>        b text, 
regress(>        PRIMARY KEY(a,b)
regress(>     );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "demo_pkey" for table "demo"
CREATE TABLE

Этот индекс можно увидеть при просмотре таблицы:

regress=> \d demo
     Table "public.demo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | not null
 b      | text    | not null
Indexes:
    "demo_pkey" PRIMARY KEY, btree (a, b)

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

Одним из следствий неотъемлемого упорядочения индекса (но не кучи) является то, что гораздо быстрее искать:

SELECT * FROM demo ORDER BY a, b;
SELECT * FROM demo ORDER BY a;

чем:

SELECT * FROM demo ORDER BY a DESC, b;

и ни один из них не может использовать индекс первичного ключа вообще, они будут делать seqscan, если у вас нет индекса на b:

SELECT * FROM demo ORDER BY b, a;
SELECT * FROM demo ORDER BY b;

Это becaues PostgreSQL может использовать индекс на (a,b) почти так же быстро, как индекс только на (a). Он не может использовать индекс на (a,b), как если бы он был индексом только на (b) - даже не медленно, он просто не может.

Как и для записи DESC, для этого Pg должен выполнить обратное сканирование индекса, которое медленнее обычного сканирования прямого индекса. Если вы просматриваете большое количество обратных индексов в EXPLAIN ANALYZE, и вы можете себе позволить стоимость исполнения дополнительного индекса, вы можете создать индекс в поле в DESC порядке.

Это относится к предложениям WHERE, а не только к ORDER BY. Вы можете использовать индекс (a,b) для поиска WHERE a = 4 или WHERE a = 4 AND b = 3, но не для поиска только WHERE b = 3.