Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 825D62E009D for ; Tue, 17 Jun 2008 09:02:56 -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 31381-01-3 for ; Tue, 17 Jun 2008 09:02:39 -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 DD3472E0092 for ; Tue, 17 Jun 2008 09:02:04 -0300 (ADT) Received: (qmail 13881 invoked from network); 17 Jun 2008 12:02:01 -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 5.967018 secs); 17 Jun 2008 12:02:01 -0000 Received: from unknown (HELO ?10.202.6.101?) (selestial-howard.cole@62.3.231.11) by ds528.dedicated.turbodns.co.uk with SMTP; 17 Jun 2008 12:01:55 -0000 Message-ID: <4857A739.5020800@selestial.com> Date: Tue, 17 Jun 2008 12:59:53 +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> 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/65 X-Sequence-Number: 30415 Matthew Wakeling wrote: > On Tue, 17 Jun 2008, Howard Cole wrote: >> They both appear to do a scan on the email table (Why?). > > The indexes don't contain copies of the row data. They only contain > pointers to the rows in the table. So once the index has been > consulted, Postgres still needs to look at the table to fetch the > actual rows. Of course, it only needs to bother looking where the > index points, and that is the benefit of an index. > > Matthew > Thanks for your patience with me here Matthew, But what I don't understand is why it needs to do a scan on email. If I do a query that uses another index, then it uses the index only and does not scan the email table. The scan on the fts index takes 6 seconds, which presumably returns email_id's (the email_id being the primary key) - what does it then need from the email table that takes 22 seconds? e.g. triohq=> explain select email_id from email where email_directory_id=1; QUERY PLAN -------------------------------------------------------------------------------- ------------- Index Scan using email_email_directory_id_idx on email (cost=0.00..129.01 rows =35 width=8) Index Cond: (email_directory_id = 1) (2 rows)