From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Ivan Schneider <ivan@doctolib.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Autocompletion with full text search
Date: Fri, 30 Jan 2015 19:57:31 -0600
Message-ID: <54CC368B.9060602@BlueTreble.com> (raw)
In-Reply-To: <CAJ+OpjCtV_XhhHF8PK6U2sW=jLbjGFS6j_uXKNhAfdW+3ZtSVw@mail.gmail.com>
References: <CAJ+OpjCtV_XhhHF8PK6U2sW=jLbjGFS6j_uXKNhAfdW+3ZtSVw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, ivan@doctolib.fr
Subject: Re: Autocompletion with full text search
In-Reply-To: <54CC368B.9060602@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox