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