Search Postgresql Archives

Re: FTS query, statistics and planner estimations…

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

Attachment: signature.asc
Description: This is a digitally signed message part.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux