On Wed, May 2, 2012 at 2:50 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
It looks like the problem is with the estimate of the antijoin size:
that is, only about 20% of the rows in sb_messages are eliminated by the
> -> Nested Loop Anti Join (cost=0.00..24576.82 rows=1 width=206)
> (actual time=0.043..436.386 rows=20761 loops=1)
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
