Message-ID: <5114A183.3090502@krogh.cc>
Date: Fri, 08 Feb 2013 07:56:03 +0100
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: Stefan Keller <sfkeller@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: FTS performance issue probably due to wrong planner
	estimate of detoasting
References: 
 <CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com>
In-Reply-To: 
 <CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 08/02/13 01:52, Stefan Keller wrote:
> Hi,
>
> I have problems with the performance of FTS in a query like this:
>
>    SELECT * FROM FullTextSearch WHERE content_tsv_gin @@
> plainto_tsquery('english', 'good');
>
> It's slow (> 30 sec.) for some GB (27886 html files, originally 73 MB zipped).
> The planner obviously always chooses table scan: http://explain.depesz.com/s/EEE
> I have to check again, if I'm doing something wrong but I'm pretty
> sure it has to do with de-toasting and (wrong?) cost estimations.
If you havent done it .. bump up statistics target on the column and
re-analyze, see what that gives.

I have also been playing with the cost-numbers in order to get it to favour
an index-scan more often. That is lowering random_page_cost to be close to
seq_page_cost, dependent on your system, the amount of memory, etc, then
this can have negative side-effects on non-gin-queries.

-- 
Jesper


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance