From: Alan Hodgson <ahodgson@simkin.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 Initial Search Speed
Date: Mon, 16 Jun 2008 11:24:57 -0700
Message-ID: <200806161124.57543@hal.medialogik.com> (raw)
In-Reply-To: <4856A91F.1020001@selestial.com>
References: <4856A91F.1020001@selestial.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
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: ahodgson@simkin.ca
Subject: Re: Tsearch2 Initial Search Speed
In-Reply-To: <200806161124.57543@hal.medialogik.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