Search Postgresql Archives

Re: Problem with planner

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

 



hubert depesz lubaczewski <depesz@xxxxxxxxxx> writes:
> as for good plan. sure. If i'll disable bitmap scans, I get:

> $ explain analyze select count(*) from objects where state='active' and ending_tsz <= (select now() - '1 day'::interval );
>                                                                        QUERY PLAN                                                                        
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=7651119.35..7651119.36 rows=1 width=0) (actual time=63.150..63.151 rows=1 loops=1)
>    InitPlan
>      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)
>    ->  Index Scan using objects_ending_tsz_active on objects  (cost=0.00..7642758.99 rows=3344138 width=0) (actual time=63.131..63.131 rows=0 loops=1)
>          Index Cond: (ending_tsz <= $0)
>  Total runtime: 63.279 ms
> (6 rows)

There's absolutely no hope of getting the planner to make intelligent
plan choices when its estimates are that far off (3344138 rows estimated
versus none actual).  It's just luck if you get the "right" plan despite
that.

Now part of the reason why the estimate is so bad is the sub-select,
which completely defeats selectivity estimation for the ending_tsz
condition.  Could we see EXPLAIN ANALYZE for the query without a
sub-select, both with and without enable_bitmapscan?

			regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[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