On Wed, Aug 26, 2009 at 6:29 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> gael@pilotsystems.net (=?iso-8859-1?Q?Ga=EBl?= Le Mignot) writes:
>> So it seems it was quite wrong about estimated matching rows (192 predicted, 10222 reals).
>
> Yup. What's even more interesting is that it seems the real win would
> have been to use just the 'claude & duviau' condition (which apparently
> matched only 14 rows). 8.3 had no hope whatever of understanding that,
> it just got lucky. 8.4 should have figured it out, I'm thinking.
> Does it help if you increase the statistics target for fulltext_tsv?
> (Don't forget to re-ANALYZE after doing so.)
It could be interesting to run the query without the condition
(keywords_tsv @@ '''assassinat'''::tsquery) to see the estimate of
(fulltext_tsv @@ '''claud'' & ''duviau'''::tsquery) in 8.4.
Btw, what Tom means by increasing the statistics is executing the
following queries:
ALTER TABLE libeindex ALTER COLUMN fulltext_tsv SET STATISTICS 500;
ANALYZE;
run your query with EXPLAIN ANALYZE;
ALTER TABLE libeindex ALTER COLUMN fulltext_tsv SET STATISTICS 1000;
ANALYZE;
run your query with EXPLAIN ANALYZE;
ALTER TABLE libeindex ALTER COLUMN fulltext_tsv SET STATISTICS 5000;
ANALYZE;
run your query with EXPLAIN ANALYZE;
to see if it improves the estimates.
--
Guillaume
