Re: FTS query, statistics and planner estimations…

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.

--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company