Агрегаты в БД — многомерные суперагрегаты

[akhkmed]

Индекс под него — (quant, it, wh, dt). Почему на первом месте именно quant? Потому что у него очень маленькая селективность, и индекс будет занимать меньше места.

Подскажите, это действительно так, что размер индекса действительно зависит от того, более или менее селективный столбец стоит на 1 месте в списке столбцов индекса?

Контрпример ниже.

create table tst(
  id serial,
  goods_id integer,
  quant char,
  value numeric);

insert into tst(goods_id, quant, value)
  select abs(g.id), q.q , v.v::numeric
    from generate_series(1, 10000000, 1) as g(id)
   cross join lateral (select random() v
                        where g.id is not null) v
   cross join lateral (select (array['d','m','y'])[abs(hashint4(g.id)) % 3 + 1] as q ) q;

select count(distinct goods_id), count(distinct quant) --10000000, 3
  from tst;

create index idx1 on tst(goods_id, quant);
create index idx2 on tst(quant, goods_id);

select pg_relation_size('idx1'::regclass), 'idx1'--224641024, idx1
union all
select pg_relation_size('idx2'::regclass), 'idx2';--224641024, idx2

То же самое (по размеру), хотя селективность первого столбца отличается в 3333333.[3] раза.

[rrrad]

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

dbfiddle.uk/?rdbms=postgres_12&fiddle=c275004b738850408225c20f348df32f

Из личного опыта на 9.6, размер индекса на больших (десятки миллионов строк) таблицах, в которых каждая строка может быть изменена неоднократно, достаточно сильно зависит от порядка полей, при чём выгоднее первым разместить более селективное поле. Вроде, в более поздних версиях что-то делали для оптимизации занимаемого пространства слабоселективных индексов, но не факт, что это нивелирует эффект эксплуатационных накладных расходов.

[akhkmed]

В Вашем примере есть небольшая неточность, в нём не селективность влияет на размер, а порядок добавления элементов.

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

create table tst(
  id serial,
  goods_id integer,
  quant char,
  value numeric);

create index idx1 on tst(goods_id, quant);
create index idx2 on tst(quant, goods_id);

insert into tst(goods_id, quant, value)
  select abs(hashint4(g.id)), q.q , v.v::numeric
    from generate_series(1, 10000000, 1) as g(id)
   cross join lateral (select random() v
                        where g.id is not null) v
   cross join lateral (select (array['d','m','y'])[abs(hashint4(g.id)) % 3 + 1] as q ) q;

select count(distinct goods_id), count(distinct quant) --9976463, 3
  from tst;

select pg_relation_size('idx1'::regclass), 'idx1'--294600704, idx1
union all
select pg_relation_size('idx2'::regclass), 'idx2';--291512320, idx2

Селективность первого столбца отличается в 3325487.[6] раза, но картина вышла противоположная Вашему примеру.

[Kilor]

PostgreSQL 10:

CREATE TABLE test(a integer, b integer);

CREATE INDEX testidx_ab ON test(a, b);
CREATE INDEX testidx_ba ON test(b, a);

INSERT INTO test
SELECT
  (random() * 100)::integer a
, (random() * 10000)::integer b
FROM
  generate_series(1, 1000000) i; -- данных должно быть существенно много

SELECT pg_relation_size('testidx_ab'::regclass); -- 29270016
SELECT pg_relation_size('testidx_ba'::regclass); -- 29589504, +1.1%

Как-то так… Замечу, что после VACUUM FULL размеры сравняются за счет перебалансировки деревьев — 22487040 (-23%!), но до этого момента BA-дерево явно содержит больше узлов.