Re: [GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

2016-11-09 10:40 GMT+01:00 Francisco Olarte <folarte@xxxxxxxxxxxxxx>:

Pierre:

On Wed, Nov 9, 2016 at 10:22 AM, Pierre Ducroquet
<pierre.ducroquet@people-doc.com> wrote:
> 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.
....
> but the issue remain the same. The table contains about 295,000 documents, and
....
>  Request                          | Estimated rows | Real rows
> ----------------------------------+----------------+-----------
> 'word1'                           | 38050          | 37500
> 'word1 word2'                     | 4680           | 32000
> 'word1 word2 word3'               | 270            | 12300
> 'word1 word2 word3 word4'         | 10             | 9930
> 'word1 word2 word3 word4 word5'   | 1              | 9930
>
> You can see that with more words in query, the estimation falls far behind
> reality.

I'm not really familiar with FTS but, doing a few division of
estimations and rows it seems it estimates as uncorrelated words, and
you real rows clearly indicate some of them are clearly correlated (
like w1/w2 and w4/s5, and partially w3/w45 ) and very common.

> Is that a known limitation of the FTS indexing ? Am I missing something
> obvious, or a poor configuration ?

Someone more familiar with it needed for that, but what I've found
several times is FTS does not mix too well with relational queries at
the optimizer level ( as FTS terms can have very diverse degrees of
correlation, which is very difficult to store in the statistics a
relational optimizer normally uses ).

there is workaround - the FTS query can be wrapped to immutable function - then can be executed in planner time, and the estimations can be better

 http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Using_IMMUTABLE_functions_as_hints_for_the_optimizer

Regards

Pavel

Francisco Olarte.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general