Re: planner row-estimates for tsvector seems horribly wrong

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