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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
