Search Postgresql Archives

Re: Inefficient plan selected by PostgreSQL 9.0.7

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Wed, May 2, 2012 at 2:50 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Maxim Boguk <maxim.boguk@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux