Re: Completely wrong row estimates

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

In response to

Responses

Browse pgsql-general by date

  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