Re: planner row-estimates for tsvector seems horribly wrong

Thanks a ton Jan! It works quite correctly. But many tsearch tutorials
ask tsquery to be placed in 'from' statement and that can cause bad
plan. Isn't it possible to return the correct number for a join with the
query as well?

-Sushant.

On Sun, 2010-10-24 at 15:07 +0200, Jan Urbański wrote:
> On 24/10/10 14:44, Sushant Sinha wrote:
> > I am using gin index on a tsvector and doing basic search. I see the
> > row-estimate of the planner to be horribly wrong. It is returning
> > row-estimate as 4843 for all queries whether it matches zero rows, a
> > medium number of rows (88,000) or a large number of rows (726,000).
> >
> > The table has roughly a million docs.
>
> > explain analyze select count(*) from docmeta,
> > plainto_tsquery('english', 'dyfdfdf') as qdoc where docvector @@ qdoc;
>
> OK, forget my previous message. The problem is that you are doing a join
> using @@ as the operator for the join condition, so the planner uses the
> operator's join selectivity estimate. For @@ the tsmatchjoinsel function
> simply returns 0.005.
>
> Try doing:
>
> explain analyze select count(*) from docmeta where docvector @@
> plainto_tsquery('english', 'dyfdfdf');
>
> It should help.
>
> Cheers,
> Jan