Skip site navigation (1) Skip section navigation (2)

Re: Tsearch2 Initial Search Speed

From: Howard Cole <howardnews(at)selestial(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Tsearch2 Initial Search Speed
Date: 2008-06-17 11:59:53
Message-ID: 4857A739.5020800@selestial.com (view raw or flat)
Thread:
Lists: pgsql-performance
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)

In response to

Responses

pgsql-performance by date

Next:From: sathiya psqlDate: 2008-06-17 13:13:18
Subject: Migration Articles.. ???
Previous:From: Matthew WakelingDate: 2008-06-17 11:30:48
Subject: Re: Tsearch2 Initial Search Speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group