From: | Artur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> |
---|---|
To: | Pierre Ducroquet <pierre(dot)ducroquet(at)people-doc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: FTS query, statistics and planner estimations… |
Date: | 2016-11-09 10:01:29 |
Message-ID: | 83ac191b-be41-4ad3-7f39-ece3f627a554@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pierre Ducroquet | 2016-11-09 10:11:31 | Re: FTS query, statistics and planner estimations… |
Previous Message | Pavel Stehule | 2016-11-09 09:51:11 | Re: [GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations… |