Den 5 april 2010 11.57 skrev Magnus Hagander <magnus@xxxxxxxxxxxx>: >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
