Re: Tsearch2 Initial Search Speed

On Tue, 17 Jun 2008, Howard Cole wrote:
> If I do a query that uses another index, then it uses the index only and
> does not scan the email table.

Not true. It only looks a little bit like that from the explain output.
However, if you look closely:

> 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)

It's a scan *using* the index, but *on* the table "email". This index scan
is having to read the email table too.

> 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?

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

--
First law of computing: Anything can go wro
sig: Segmentation fault. core dumped.