From: Howard Cole <howardnews@selestial.com>
To: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 Initial Search Speed
Date: Tue, 17 Jun 2008 12:59:53 +0100
Message-ID: <4857A739.5020800@selestial.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0806171228480.3987@aragorn.flymine.org>
References: <4856A91F.1020001@selestial.com>
<200806161124.57543@hal.medialogik.com>
<485789C1.3000803@selestial.com>
<485797D4.7010201@selestial.com>
<Pine.LNX.4.64.0806171202130.3987@aragorn.flymine.org>
<48579EB1.8050403@selestial.com>
<Pine.LNX.4.64.0806171228480.3987@aragorn.flymine.org>
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)
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: howardnews@selestial.com
Subject: Re: Tsearch2 Initial Search Speed
In-Reply-To: <4857A739.5020800@selestial.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