Поиск по 300 миллионам адресов с помощью pg_trgm

Question

У меня 300 миллионов адресов в моей БД PostgreSQL 9.3, и я хочу использовать pg_trgm для нечеткого поиска строк. Конечная цель - реализовать функцию поиска, аналогичную поиску по карте Google.

Когда я использовал pg_trgm для поиска по этим адресам, получение результатов стоило около 30 секунд. Есть много строк, соответствующих пороговому условию сходства по умолчанию, равному 0,3, но мне просто нужно около 5 или 10 результатов. Я создал триграмму индекса GiST:

CREATE INDEX addresses_trgm_index ON addresses USING gist (address gist_trgm_ops);

Это мой запрос:

SELECT address, similarity(address, '981 maun st') AS sml 
FROM addresses 
WHERE address % '981 maun st' 
ORDER BY sml DESC 
LIMIT 10;

Тестовая таблица по производственной среде была удалена. Я показываю EXPLAIN вывод из моей тестовой среды. Есть около 7 миллионов строк, и для получения результатов требуется около 1,6 с. С 300 миллионами ему нужно больше 30 с.

ebdb=> explain analyse select address, similarity(address, '781 maun st') as sml from addresses where address % '781 maun st' order by sml desc limit 10;
                                    QUERY PLAN                                                                            
————————————————————————————————————————————————————————————————————————————————    
 Limit  (cost=7615.83..7615.86 rows=10 width=16) (actual time=1661.004..1661.010 rows=10 loops=1)
 ->  Sort  (cost=7615.83..7634.00 rows=7268 width=16) (actual time=1661.003..1661.005 rows=10 loops=1)
     Sort Key: (similarity((address)::text, '781 maun st'::text))
     Sort Method: top-N heapsort  Memory: 25kB
     ->  Index Scan using addresses_trgm_index on addresses  (cost=0.41..7458.78 rows=7268 width=16) (actual time=0.659..1656.386 rows=5241 loops=1)
           Index Cond: ((address)::text % '781 maun st'::text)
 Total runtime: 1661.066 ms
(7 rows)

Есть ли хороший способ улучшить производительность или это хороший план для разделения таблиц?

Answer 1

PostgreSQL 9.3 ... Есть ли хороший способ повысить производительность или это хороший план для разделения таблиц?

Разделение таблиц не поможет вообще.

Но да, есть хороший способ: перейти на текущую версию Postgres. Было много улучшений для индексов GiST, в частности для модуля pg_trgm и для больших данных в целом. Должно быть значительно быстрее с Postgres 9.6 или предстоящим Postgres 10 (в настоящее время бета).

Вы "ближайший сосед" выглядит правильно, но для небольшого LIMIT используйте вместо этого эквивалентный запрос:

SELECT address, similarity(address, '981 maun st') AS sml 
FROM   addresses 
WHERE  address % '981 maun st' 
ORDER  BY address <-> '981 maun st'
LIMIT  10;

Цитирование руководства:

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