On Wed, May 2, 2012 at 2:50 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Maxim Boguk <maxim.bo...@gmail.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