From: Jesper Krogh <jesper@krogh.cc>
To: Stefan Keller <sfkeller@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: FTS performance issue probably due to wrong planner estimate of detoasting
Date: Fri, 08 Feb 2013 07:56:03 +0100
Message-ID: <5114A183.3090502@krogh.cc> (raw)
In-Reply-To: <CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com>
References: <CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: jesper@krogh.cc, sfkeller@gmail.com
Subject: Re: FTS performance issue probably due to wrong planner estimate of detoasting
In-Reply-To: <5114A183.3090502@krogh.cc>
* 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