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

Re: Tsearch2 Initial Search Speed

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Tsearch2 Initial Search Speed
Date: 2008-06-17 13:13:27
Message-ID: Pine.LNX.4.64.0806171407250.3987@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2008-06-17 13:23:00
Subject: Re: [PERFORM] Migration Articles.. ???
Previous:From: sathiya psqlDate: 2008-06-17 13:13:18
Subject: Migration Articles.. ???

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