From: | Björn Lindqvist <bjourne(at)gmail(dot)com> |
---|---|
To: | Magnus Hagander <magnus(at)hagander(dot)net> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Completely wrong row estimates |
Date: | 2010-04-06 11:28:47 |
Message-ID: | z2o740c3aec1004060428r689fa491kdff293f721ade7a0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Den 5 april 2010 11.57 skrev Magnus Hagander <magnus(at)hagander(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Mead | 2010-04-06 12:22:00 | Re: Completely wrong row estimates |
Previous Message | Peter Geoghegan | 2010-04-06 10:02:47 | Re: Fuzzy string matching of product names |