postgres не использует индекс btree_gist

Question

У меня есть огромная таблица с первичным ключом и индексом btree_gist. Когда я запрашиваю столбцы в индексе btree_gist, я ожидаю, что индекс используется, и запрос выполняется довольно быстро. Однако оптимизатор всегда выполняет сканирование индекса по первичному ключу и фильтрам.

Пример:

      create table test1 (
    id1 bigint not null,
    id2 bigint not null,
    validtime tstzrange not null,
    data float);
alter table test1 add constraint pk_test1 primary key (id1, id2, validtime);
alter table test1 add constraint ex_test1_validtime exclude using gist (id1 with =, id2 with =, validtime with &&);

Таблица содержит около 1,2 миллиарда строк, запрос, который меня интересует, возвращает всего несколько сотен строк, но занимает много времени:

      select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
(about 3s)

План запроса:

      explain select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Index Scan using pk_test1 on test1  (cost=0.70..24.68 rows=1 width=46)
   Index Cond: ((id1 = 1) AND (id2 = 1))
   Filter: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

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

Интересно, почему postgres не использует файл btree_gist.

---

У меня есть другая, немного другая таблица, в которой используется btree_gist, но совсем не так, как я ожидал. В этой таблице около 160 миллионов строк.

      create table test2 (
    id1 bigint not null,
    validtime tstzrange not null);                                                                                                                                                                                                                                                                                                                                              
alter table test2 add constraint pk_test2 primary key (id1, validtime);
alter table test2 add constraint ex_test2_validtime exclude using gist (id1 with =, validtime with &&);

Здесь план выполнения выглядит так:

      select * from test2 where id1=1 and validtime && '[2020-01-01,2020-02-01)';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test2  (cost=1933.19..1937.20 rows=1 width=62)
   Recheck Cond: ((id1 = 1) AND (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange))
   ->  BitmapAnd  (cost=1933.19..1933.19 rows=1 width=0)
         ->  Bitmap Index Scan on pk_test2  (cost=0.00..574.20 rows=11417 width=0)
               Index Cond: (id1 = 1)
         ->  Bitmap Index Scan on ex_test2_validtime  (cost=0.00..1358.74 rows=17019 width=0)
               Index Cond: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

Почему два сканирования растрового индекса, нельзя ли все это сделать за одно сканирование индекса с использованием индекса btree_gist?

Answer 1

Наконец нашел:

Индексы не используются из-за несоответствия типов между запросом и индексом. На самом деле это упоминается повсюду, но я просто прочитал это.

явно не является ! Забавно, что приведение происходит автоматически при использовании первичного ключа btree, а не btree_gist.

Во всяком случае, с этим запросом все работает, как и ожидалось:

      select * from test1
where id1=1::bigint and id2=1::bigint
and validtime && '[2020-01-01,2020-02-01)';

Изучение этого стоило мне нескольких часов, я никогда больше этого не забуду!

Answer 2

Ваш ответ правильный, но я хочу добавить немного информации о том, почему это происходит.

Индекс в PostgreSQL поддерживает только операторы, принадлежащие к семейству операторов его класса операторов . Для индексов GiST на , то есть

      SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'gist'
  AND ao.amoplefttype = 'bigint'::regtype;

 amoplefttype │ oprname │ amoprighttype 
══════════════╪═════════╪═══════════════
 bigint       │ <       │ bigint
 bigint       │ <=      │ bigint
 bigint       │ =       │ bigint
 bigint       │ >=      │ bigint
 bigint       │ >       │ bigint
 bigint       │ <>      │ bigint
 bigint       │ <->     │ bigint
(7 rows)

что объясняет, почему вы должны выполнить приведение к индексу, чтобы его можно было использовать.

Это удивительно, если вы привыкли к PostgreSQL, потому что PostgreSQL не нуждается в таком приведении с индексом B-дерева. Объяснение состоит в том, что семейство операторов для btreeимеет больше операторов:

      SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'btree'
  AND ao.amoplefttype = 'bigint'::regtype;

 amoplefttype │ oprname │ amoprighttype 
══════════════╪═════════╪═══════════════
 bigint       │ <       │ bigint
 bigint       │ <=      │ bigint
 bigint       │ =       │ bigint
 bigint       │ >=      │ bigint
 bigint       │ >       │ bigint
 bigint       │ <       │ smallint
 bigint       │ <=      │ smallint
 bigint       │ =       │ smallint
 bigint       │ >=      │ smallint
 bigint       │ >       │ smallint
 bigint       │ <       │ integer
 bigint       │ <=      │ integer
 bigint       │ =       │ integer
 bigint       │ >=      │ integer
 bigint       │ >       │ integer
(15 rows)

и сравнение равенства между bigintа также integerнаходится среди них.

Вы могли бы использовать обычный индекс B-дерева для поддержки вашего запроса, если бы вы написали условие, используя >=а также <скорее, чем &&, что сделало бы приведение ненужным, но, конечно, вы не хотите создавать второй индекс, если уже есть индекс из ограничения исключения.