Den 5 april 2010 11.57 skrev Magnus Hagander <magnus@hagander.net>:
>> Note how the planner estimates that there are 766 rows in the table
>> that matches the word 'tagtext'. In reality 43374 does. I've tried to
>> get postgres to refresh the statistics by running with
>> enable_statistics_target=100, running VACUUM, VACUUM FULL, VACUUM FULL
>> ANALYZE etc but nothing works. Postgres seem stuck with its bad
>> statistics and unwilling to change them. There are many other strings
>> that also matches tens of thousands of rows in the table which
>> postgres only thinks matches 766.
>
> I assume you mean default_statistics_target, not enable_statistics_target.
Yes, sorry.
> You should try setting it higher - but obviously just for these
> columns. Use something like
>
> ALTER TABLE words ALTER COLUMN word SET STATISTICS 1000
>
> Then you need to run ANALYZE on it. Not vacuum ,certainly not vacuum
> full, just analyze.
Done that and it doesn't help. The estimates are always off for the
query of the type I specified.
> Oh, and if what you're doing is actually full text search, which is
> what it looks like, you should really look at using the native full
> text indexing support rather than just stuffing your words in a table.
> You'll get better and much faster results.
It is more "full tag search" because I'm not using any word stemming,
phrase matching or OR:ing query terms. It was, when I measured it,
significantly faster than using the native text searching feature.
--
mvh Björn