Re: Problem with planner

hubert depesz lubaczewski <depesz(at)depesz(dot)com> 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