Re: Inefficient plan selected by PostgreSQL 9.0.7

On Wed, May 2, 2012 at 2:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> > I got very inefficient plan for a simple query.
>
> It looks like the problem is with the estimate of the antijoin size:
>
> > -> Nested Loop Anti Join (cost=0.00..24576.82 rows=1
> width=206)
> > (actual time=0.043..436.386 rows=20761 loops=1)
>
> that is, only about 20% of the rows in sb_messages are eliminated by the
> NOT EXISTS condition, but the planner thinks that nearly all of them
> will be (and that causes it to not think that the LIMIT is going to
> affect anything, so it doesn't prefer a fast-start plan).
>
> Since you've not told us anything about the statistics of these tables,
> it's hard to speculate as to why the estimate is off.
>
> regards, tom lane
>

Hi,

Is there any particular stat data what I need provide except these two:

SELECT * from pg_stats where tablename='users' and attname='blocked';
-[ RECORD 1 ]-----+--------------------
schemaname | public
tablename | users
attname | blocked
inherited | f
null_frac | 0
avg_width | 1
n_distinct | 2
most_common_vals | {f,t}
most_common_freqs | {0.573007,0.426993}
histogram_bounds |
correlation | 0.900014

and

SELECT
schemaname,tablename,attname,inherited,null_frac,avg_width,n_distinct,correlation
from pg_stats where tablename='sb_messages' and attname='from_user';
-[ RECORD 1 ]------------
schemaname | public
tablename | sb_messages
attname | from_user
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 103473
correlation | 0.512214

(most_common_vals, most_common_freqs and histogram_bounds is very long
values from default_statistics_target=1000, top most_common_freqs is only
0.00282333).

Kind Regards,
Maksym