From: Alan Hodgson <ahodgson@simkin.ca>
Organization: Simkin Network Consulting
To: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 Initial Search Speed
Date: Mon, 16 Jun 2008 11:24:57 -0700
User-Agent: KMail/1.9.9
References: <4856A91F.1020001@selestial.com>
In-Reply-To: <4856A91F.1020001@selestial.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200806161124.57543@hal.medialogik.com>

On Monday 16 June 2008, Howard Cole <howardnews@selestial.com> wrote:
> Hi, I am looking to improve the initial query speed for the following
> query:
>
> select email_id from email, to_tsquery('default','example') as q where
> q@@fts;
>
> This is running on 8.2.4 on Windows Server 2K3.
>
> The initial output from explain analyse is as follows.
>
> "Nested Loop  (cost=8.45..76.70 rows=18 width=8) (actual
> time=5776.347..27364.248 rows=14938 loops=1)"
> "  ->  Function Scan on q  (cost=0.00..0.01 rows=1 width=32) (actual
> time=0.023..0.024 rows=1 loops=1)"
> "  ->  Bitmap Heap Scan on email  (cost=8.45..76.46 rows=18 width=322)
> (actual time=5776.314..27353.344 rows=14938 loops=1)"
> "        Filter: (q.q @@ email.fts)"
> "        ->  Bitmap Index Scan on email_fts_index  (cost=0.00..8.44
> rows=18 width=0) (actual time=5763.355..5763.355 rows=15118 loops=1)"
> "              Index Cond: (q.q @@ email.fts)"
> "Total runtime: 27369.091 ms"
>
> Subsequent output is considerably faster. (I am guessing that is because
> email_fts_index is cached.

It's because everything is cached, in particular the relevant rows from 
the "email" table (accessing which took 22 of the original 27 seconds).

The plan looks good for what it's doing.

I don't see that query getting much faster unless you could add a lot more 
cache RAM; 30K random IOs off disk is going to take a fair bit of time 
regardless of what you do. 

-- 
Alan