2010/4/6 Björn Lindqvist <bjou...@gmail.com> > Den 5 april 2010 11.57 skrev Magnus Hagander <mag...@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. >
Have you tried running : 'EXPLAIN ANALYZE <your query>' ? This will show you the estimates and the actuals (for each operation) side by side. --Scott > > > > 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
