Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UQIxg-0007QU-DZ for pgsql-performance@arkaria.postgresql.org; Thu, 11 Apr 2013 14:58:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UQIxf-00022z-P7 for pgsql-performance@arkaria.postgresql.org; Thu, 11 Apr 2013 14:58:51 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UQIxe-00022r-M0 for pgsql-performance@postgresql.org; Thu, 11 Apr 2013 14:58:50 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UQIxc-0003At-1q for pgsql-performance@postgresql.org; Thu, 11 Apr 2013 14:58:50 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r3BEwkic002752; Thu, 11 Apr 2013 10:58:46 -0400 (EDT) From: Tom Lane To: Luigi Saggese cc: pgsql-performance@postgresql.org Subject: Re: Performance ts_vector fulltext search In-reply-to: References: Comments: In-reply-to Luigi Saggese message dated "Thu, 11 Apr 2013 09:47:20 +0200" Date: Thu, 11 Apr 2013 10:58:46 -0400 Message-ID: <2751.1365692326@sss.pgh.pa.us> X-Pg-Spam-Score: -4.3 (----) 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 Luigi Saggese writes: > I've configured 2 table like this > ... > CREATE INDEX "ix_fulltext_usp_what" ON "public"."User_Statement_Pivot" > ("to_tsvector('italian'::regconfig, ""What""::text)", > "to_tsvector('italian'::regconfig, ""What""::text)"); When I try that I get ERROR: column "to_tsvector('italian'::regconfig, "What"::text)" does not exist as indeed I should, because you seem to be confused about SQL quoting rules --- that whole expression is being taken as a name. However, the bigger problem here is that you're creating btree indexes, which are not useful for full text search. They need to be gin or gist indexes. See examples at http://www.postgresql.org/docs/9.2/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance