Re: Autocompletion with full text search

On 1/15/15 6:41 PM, Ivan Schneider wrote:
>
> We implemented an autocompletion feature (case and accent insensitive)
> using PostgreSQL full text search.
> The query fetches patient ids matching the full text query that belong
> to a given patient base (rows contain a pg_array with patient_base_ids).
> Our table grew over time (6.2 million rows now) and the query got
> slower. We are wondering if we have hit the limit or if there is still
> room for performance improvement with better indexing or data
> partitioning for instance.
> Here is a link to the "explain (analyze, buffers)" results from such a
> query run on one of our servers : http://explain.depesz.com/s/a5Q9
> Running analyze on the table doesn't change the results and the table is
> autovacuumed (last one was 2015-01-08 22:18).
>

The query time is consumed by scanning the index, which at 152ms doesn't
seem all that bad. Have you tried reindexing? That might help. You could
also try something like trigram
(http://www.postgresql.org/docs/9.1/static/pgtrgm.html) it might be faster.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com