2010/4/6 Björn Lindqvist <bjourne@xxxxxxxxx>
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.
Have you tried running :
'EXPLAIN ANALYZE <your query>'
?
This will show you the estimates and the actuals (for each operation) side by side.
--Scott
>Yes, sorry.
> I assume you mean default_statistics_target, not enable_statistics_target.
Done that and it doesn't help. The estimates are always off for the
> 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.
query of the type I specified.
It is more "full tag search" because I'm not using any word stemming,
> 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.
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
