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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
