Всем привет. Вопрос. в sql не силен entity_id bigint т.е. полнотекстовый поиск

по числу нужен

запрос типа:

```EXPLAIN ANALYZE
SELECT result.entity_id
FROM (SELECT distinct t.entity_id
FROM linked_carriage t
WHERE t.is_deleted = 'f' AND t.entity_id::text LIKE '%1163062%') AS result
LIMIT 10 OFFSET 0```

причем тут вместо %1163062% может быть от %116%

без индекса
```Limit (cost=0.28..121.82 rows=1 width=8) (actual time=0.441..1.073 rows=1 loops=1)
-> Unique (cost=0.28..121.81 rows=1 width=8) (actual time=0.440..1.071 rows=1 loops=1)
-> Index Only Scan using idx_linked_carriage_entity_id_entity_type_is_deleted_chain_id_u on linked_carriage t (cost=0.28..121.81 rows=1 width=8) (actual time=0.438..1.068 rows=2 loops=1)
Filter: ((entity_id)::text ~~ '%1163062%'::text)
Rows Removed by Filter: 3521
Heap Fetches: 158
Planning Time: 0.168 ms
Execution Time: 1.097 ms```

c индексом
```create index concurrently idx_linked_carriage_entity_id_tgrm on linked_carriage using gin (cast(entity_id as text) gin_trgm_ops);```

```Limit (cost=12.03..12.05 rows=1 width=8) (actual time=0.057..0.060 rows=1 loops=1)
-> Unique (cost=12.03..12.04 rows=1 width=8) (actual time=0.055..0.058 rows=1 loops=1)
-> Sort (cost=12.03..12.04 rows=1 width=8) (actual time=0.055..0.056 rows=2 loops=1)
Sort Key: t.entity_id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on linked_carriage t (cost=11.00..12.02 rows=1 width=8) (actual time=0.046..0.047 rows=2 loops=1)
Recheck Cond: ((entity_id)::text ~~ '%1163062%'::text)
Filter: (NOT is_deleted)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_linked_carriage_entity_id_tgrm (cost=0.00..11.00 rows=1 width=0) (actual time=0.036..0.037 rows=2 loops=1)
Index Cond: ((entity_id)::text ~~ '%1163062%'::text)
Planning Time: 0.215 ms
Execution Time: 0.239 ms```

с индексом вроде результат лучше?

ну если смотреть на время да, однако оптимизация по времени не единственный вариант оптимизации так что только вам виднее лучше стало или нет

У вас там 3000 значений будет? Ну, то есть ужэ дажэ тут да, но на копейки. Главное — смотреть надо на рабочих размерах и рабочей нагрузке. (Да, кстати, если в таблицэ будут удаления/обновления — этрму индэксу надо регулярно реиндэкс делать).

Так вышло, что там чуть больше 3000, точнее 30млн, а на таком количестве план уже другой и индекс не помог 🙁 Limit (cost=0.43..52.11 rows=10 width=8) (actual time=4761.308..4761.364 rows=10 loops=1) -> Unique (cost=0.43..137132.66 rows=26588 width=8) (actual time=4761.305..4761.359 rows=10 loops=1) -> Index Only Scan using idx_linked_carriage_entity_id_entity_type_is_deleted_chain_id_u on linked_carriage t (cost=0.43..137061.27 rows=28556 width=8) (actual time=4761.302..4761.346 rows=44 loops=1) Filter: ((entity_id)::text ~~ '149%'::text) Rows Removed by Filter: 2094368 Heap Fetches: 2094412 Planning Time: 0.667 ms Execution Time: 4761.453 ms подскажите как ускорить запрос типа SELECT distinct t.entity_id FROM linked_carriage t WHERE t.is_deleted = 'f' AND t.entity_id::text LIKE '149%' LIMIT 10 OFFSET 0 это для поиска по первым цифрам id

Так по первым или по любым цыфрам?

Вообще, тут довольно типичная ошыбка планировщика postgres. Он считает, что значения паскиданы равномерно по индэксу — а по факту они сильно сгруппированы где-то. С этим можно бороться косвенными методами — запрещать там какие-то виды сканов, заставлять что-то вычислять побрльшэ, ломать использование некоторых индэксов и т.д. Дажэ увеличить лимит до сотен можэт помочь. Но в общем это сложно всё, потому лучшэ сначала получить ответ на предыдущий мой вопрос — интересуют начальные цыфры или любые.

по первым

Точнее было по люым ‘%149%’ но решили что нафиг это не нужно и оставили только ’149%’

Тогда — тупо btree индэкс, который работает на первые буквы на LIKE делай. Там collation "C" ему задать. Получится просто обычный индэкс, из которого выбирает LIKE. И в который можно докидать ещё полей, которые тебе нужны если это важно для высокой селективности или index-only скана.

вот по плану видно, что проблема в disctinct убрал, поменял на group by и стало так: EXPLAIN ANALYZE SELECT t.entity_id FROM linked_carriage t WHERE t.is_deleted = 'f' AND t.entity_id::text LIKE '149%' group by t.entity_id LIMIT 10 OFFSET 0 Limit (cost=29.73..29.83 rows=10 width=8) (actual time=0.266..0.268 rows=2 loops=1) -> HashAggregate (cost=29.73..29.95 rows=22 width=8) (actual time=0.265..0.267 rows=2 loops=1) Group Key: entity_id -> Bitmap Heap Scan on linked_carriage t (cost=7.21..29.68 rows=22 width=8) (actual time=0.257..0.262 rows=2 loops=1) Recheck Cond: ((entity_id)::text ~~ '149%'::text) Filter: (NOT is_deleted) Rows Removed by Filter: 19 Heap Blocks: exact=1 -> Bitmap Index Scan on idx_linked_carriage_entity_id_tgrm (cost=0.00..7.20 rows=27 width=0) (actual time=0.238..0.238 rows=21 loops=1) Index Cond: ((entity_id)::text ~~ '149%'::text) Planning Time: 1.939 ms Execution Time: 0.434 ms результат вроде тот же. почему distinct хуже чем group by?

Думаю — потому, что это другая база. (В первом случае у вас 10 строк вернуло, во втором — две).

Нее. База таблица та же

10ть лимит же

И в первом — 10 строк, во втором — 2. Хотя лимит 10. (Есть и ещё признаки)

Где 2? 10 же

Limit (cost=29.73..29.83 rows=10 width=8) (actual time=0.266..0.268 rows=2 loops=1)

Глюк какото Limit (cost=0.43..52.00 rows=10 width=8) (actual time=4854.875..4854.928 rows=10 loops=1) -> Group (cost=0.43..137215.15 rows=26606 width=8) (actual time=4854.871..4854.921 rows=10 loops=1) Group Key: entity_id -> Index Only Scan using idx_linked_carriage_entity_id_entity_type_is_deleted_chain_id_u on linked_carriage t (cost=0.43..137143.71 rows=28577 width=8) (actual time=4854.864..4854.905 rows=44 loops=1) Filter: ((entity_id)::text ~~ '149%'::text) Rows Removed by Filter: 2094368 Heap Fetches: 2094412 Planning Time: 0.839 ms Execution Time: 4855.022 ms Ну и чудо пропало 🙁

Бывает. А про чудо — то логично. Переходите к btree entity_id::text collate "C". Если ещё is_deleted добавите (в индэкс в начало или в includes индэкса) и entity_id в includes — будет нормально работать само.

Кстати, с btree ужэ не будет проблем добавить ORDER BY entity_id::text COLLATE "C" А то limit без order by — ну, такое себе решэние.