Есть ли способ использовать pg_trgm как оператор с индексами btree на PostgreSQL?

Question

У меня есть две таблицы:

• table_1 с ~1 миллионом строк, со столбцами id_t1: целое число, c1_t1: varchar и т. д.
• table_2 с ~50 миллионами строк, со столбцами id_t2: целое число, ref_id_t1: целое число, c1_t2: varchar и т. д.

ref_id_t1 заполнен значениями id_t1, однако они не связаны внешним ключом, так как table_2 не знает о table_1.

Мне нужно сделать запрос на обе таблицы, как показано ниже:

SELECT * FROM table_1 t1 WHERE t1.c1_t1= 'A' AND t1.id_t1 IN
(SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%');

Без каких-либо изменений или с использованием базовых индексов выполнение запроса занимает около минуты, поскольку последовательное сканирование выполняется в таблице_2. Чтобы предотвратить это, я создал GIN idex с опцией gin_trgm_ops:

CREATE EXTENSION pg_trgm;
CREATE INDEX c1_t2_gin_index ON table_2 USING gin (c1_t2, gin_trgm_ops);

Однако это не решает проблему, поскольку внутренний запрос все еще занимает очень много времени.

EXPLAIN ANALYSE SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%'

Дает следующее

Bitmap Heap Scan on table_2 t2 (cost=664.20..189671.00 rows=65058 width=4) (actual time=5101.286..22854.838 rows=69631 loops=1)
  Recheck Cond: ((c1_t2 )::text ~~ '%1.1%'::text)
  Rows Removed by Index Recheck: 49069703
  Heap Blocks: exact=611548
  ->  Bitmap Index Scan on gin_trg  (cost=0.00..647.94 rows=65058 width=0) (actual time=4911.125..4911.125 rows=49139334 loops=1)
        Index Cond: ((c1_t2)::text ~~ '%1.1%'::text)
Planning time: 0.529 ms
Execution time: 22863.017 ms

Сканирование индекса растрового изображения выполняется быстро, но по мере необходимости t2.ref_id_t1 PostgreSQL необходимо выполнить сканирование кучи растрового изображения, которое не выполняется быстро с 65000 строк данных.

Решением, позволяющим избежать сканирования кучи растровых изображений, было бы выполнить сканирование только по индексу. Это возможно при использовании нескольких столбцов с индексами btree, см. https://www.postgresql.org/docs/9.6/static/indexes-index-only-scans.html

Если я изменяю запрос, как поиск в начале c1_t2, даже если внутренний запрос возвращает 90000 строк, и если я создаю индекс btree для c1_t2 и ref_id_t1, запрос занимает чуть более секунды.

CREATE INDEX c1_t2_ref_id_t1_index
    ON table_2  USING btree
    (c1_t2 varchar_pattern_ops ASC NULLS LAST, ref_id_t1 ASC NULLS LAST)


EXPLAIN ANALYSE SELECT * FROM table_1 t1 WHERE t1.c1_t1= 'A' AND t1.id_t1 IN
    (SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE 'aaa%');

Hash Join  (cost=56561.99..105233.96 rows=1 width=2522) (actual time=953.647..1068.488 rows=36 loops=1)
  Hash Cond: (t1.id_t1 = t2.ref_id_t1)
  ->  Seq Scan on table_1 t1  (cost=0.00..48669.65 rows=615 width=2522) (actual time=0.088..667.576 rows=790 loops=1)
        Filter: (c1_t1 = 'A')
        Rows Removed by Filter: 1083798
  ->  Hash  (cost=56553.74..56553.74 rows=660 width=4) (actual time=400.657..400.657 rows=69632 loops=1)
        Buckets: 131072 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 3472kB
        ->  HashAggregate  (cost=56547.14..56553.74 rows=660 width=4) (actual time=380.280..391.871 rows=69632 loops=1)
              Group Key: t2.ref_id_t1
              ->  Index Only Scan using c1_t2_ref_id_t1_index on table_2 t2   (cost=0.56..53907.28 rows=1055943 width=4) (actual time=0.014..202.034 rows=974737 loops=1)
                    Index Cond: ((c1_t2  ~>=~ 'aaa'::text) AND (c1_t2  ~<~ 'chb'::text))
                    Filter: ((c1_t2 )::text ~~ 'aaa%'::text)
                    Heap Fetches: 0
Planning time: 1.512 ms
Execution time: 1069.712 ms

Однако это невозможно с индексами джина, так как эти индексы не хранят все данные в ключе.

Есть ли способ использовать pg_trmg как расширение с индексом btree, чтобы мы могли сканировать индекс только с LIKE запросами '%abc%'?

Answer 1

Проблема, по-видимому, была вызвана проблемой с планировщиком для этого конкретного запроса.'%1.1%'. Однако этот запрос всегда очень низкий, когда количество результатов слишком велико, поэтому я изменил структуру данных и интегрировалtable_2вtable_1вjsonbстолбец, который дает хорошие результаты во всех случаях.