hubert depesz lubaczewski <dep...@depesz.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
