Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B28262E0050 for ; Wed, 18 Jun 2008 07:42:49 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79814-08 for ; Wed, 18 Jun 2008 07:42:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ds528.dedicated.turbodns.co.uk (ds528.dedicated.turbodns.co.uk [81.21.72.118]) by developer.postgresql.org (Postfix) with ESMTP id 24FB42E004D for ; Wed, 18 Jun 2008 07:42:16 -0300 (ADT) Received: (qmail 37174 invoked from network); 18 Jun 2008 10:42:13 -0000 Received: from howardnews@selestial.com by ds528.dedicated.turbodns.co.uk by uid 1005 with qmail-scanner-1.22 ( Clear:RC:1(62.3.231.11):. Processed in 0.023827 secs); 18 Jun 2008 10:42:13 -0000 Received: from unknown (HELO ?10.202.6.101?) (selestial-howard.cole@62.3.231.11) by ds528.dedicated.turbodns.co.uk with SMTP; 18 Jun 2008 10:42:13 -0000 Message-ID: <4858E60B.5070300@selestial.com> Date: Wed, 18 Jun 2008 11:40:11 +0100 From: Howard Cole User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Tsearch2 Initial Search Speed References: <4856A91F.1020001@selestial.com> <200806161124.57543@hal.medialogik.com> <485789C1.3000803@selestial.com> <485797D4.7010201@selestial.com> <48579EB1.8050403@selestial.com> <4857A739.5020800@selestial.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/90 X-Sequence-Number: 30440 > > Actually, the index returns page numbers in the table on disc which > may contain one or more rows that are relevant. Postgres has to fetch > the whole row to find out the email_id and any other information, > including whether the row is visible in your current transaction > (concurrency control complicates it all). Just having a page number > isn't much use to you! > > Matthew > Out of interest, if I could create a multicolumn index with both the primary key and the fts key (I don't think I can create a multi-column index using GIST with both the email_id and the fts field), would this reduce access to the table due to the primary key being part of the index? More importantly, are there other ways that I can improve performance on this? I am guessing that a lot of the problem is that the email table is so big. If I cut out some of the text fields that are not needed in the search and put them in another table, presumably the size of the table will be reduced to a point where it will reduce the number of disk hits and speed the query up. So I could split the table into two parts: create table email_part2 ( email_id int8 references email_part1 (email_id), fts ..., email_directory_id ..., ) create table email_part1( email_id serial8 primary key, cc text, bcc text, ... ) and the query will be select email_id from email_part2 where to_tsquery('default', 'howard') @@ fts;