Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YHNJy-0000Lb-US for pgsql-performance@arkaria.postgresql.org; Sat, 31 Jan 2015 01:58:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YHNJy-0005q9-9x for pgsql-performance@arkaria.postgresql.org; Sat, 31 Jan 2015 01:58:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YHNJw-0005q0-6G for pgsql-performance@postgresql.org; Sat, 31 Jan 2015 01:58:00 +0000 Received: from mail-bl2on0644.outbound.protection.outlook.com ([2a01:111:f400:fc09::644] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YHNJk-0002Nf-OE for pgsql-performance@postgresql.org; Sat, 31 Jan 2015 01:57:57 +0000 Received: from decina.local (24.55.12.91) by BY1PR11MB0344.namprd11.prod.outlook.com (25.160.206.16) with Microsoft SMTP Server (TLS) id 15.1.75.20; Sat, 31 Jan 2015 01:57:41 +0000 Message-ID: <54CC368B.9060602@BlueTreble.com> Date: Fri, 30 Jan 2015 19:57:31 -0600 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Ivan Schneider , Subject: Re: Autocompletion with full text search References: In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [24.55.12.91] X-ClientProxiedBy: DM2PR10CA0051.namprd10.prod.outlook.com (10.141.241.19) To BY1PR11MB0344.namprd11.prod.outlook.com (25.160.206.16) Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; X-Microsoft-Antispam: UriScan:; X-Microsoft-Antispam: BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0344; X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004); SRVR:BY1PR11MB0344; X-Forefront-PRVS: 0473A03F3F X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(979002)(6009001)(24454002)(479174004)(377454003)(51704005)(377424004)(122386002)(77156002)(23676002)(40100003)(2950100001)(15975445007)(36756003)(62966003)(1720100001)(19580395003)(80316001)(65816999)(64126003)(87266999)(76176999)(107886001)(65956001)(66066001)(83506001)(47776003)(50986999)(87976001)(42186005)(54356999)(50466002)(33656002)(92566002)(46102003)(65806001)(59896002)(86362001)(85282002)(336755003)(18886065003)(969003)(989001)(999001)(1009001)(1019001); DIR:OUT; SFP:1101; SCL:1; SRVR:BY1PR11MB0344; H:decina.local; FPR:; SPF:None; MLV:ovrnspm; PTR:InfoNoRecords; LANG:en; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0344; X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 31 Jan 2015 01:57:41.7055 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0344 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org 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