Re: Completely wrong row estimates

2010/4/6 Björn Lindqvist <bjourne(at)gmail(dot)com>

> 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.
>

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>