Re: FTS query, statistics and planner estimations…

On Wednesday, November 9, 2016 1:01:29 PM CET you wrote:
> Hello,
>
> On 09.11.2016 12:22, Pierre Ducroquet wrote:
> > Hello
> >
> > I recently stumbled on a slow query in my database that showed an odd
> > behaviour related to the statistics of FTS queries.
> > The query does a few joins «after» running a FTS query on a main table.
> > The FTS query returns a few thousand rows, but the estimations are wrong,
> > leading the optimizer to terrible plans compared to what should happen,
> > and
> > thus creates a far higher execution time.
> > I managed to isolate the odd behaviour in a single query, and I would like
> > your opinion about it.
> >
> > I have modified the table name, columns and query to hide sensitive
> > values,
> > but the issue remain the same. The table contains about 295,000 documents,
> > and all is running under PostgreSQL 9.5.
> >
> > EXPLAIN ANALYZE
> > SELECT COUNT(*)
> > FROM documents
> > WHERE
> >
> > to_tsvector('french', subject || ' ' || body) @@
> > plainto_tsquery('XXX');
> >
> > Of course, there is an index on to_tsvector('french', subject || ' ' ||
> > body).
> Did you try in the query to specify FTS configuration:
>
> WHERE
> to_tsvector('french', subject || ' ' || body) @@
> plainto_tsquery('french', 'XXX');
>
> plainto_tsquery() with specified configuration should be faster and
> plainto_tsquery(regconfig,test) is immutable function.

Ho my bad, yes indeed I used plainto_tsquery('french', 'XXX') that is
immutable, sorry for the confusion.