Re: Problem with planner

On Tue, Aug 09, 2011 at 12:11:48PM -0400, Tom Lane wrote:
> 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?

version with disabled bitmapscans:
$ explain analyze select count(*) from objects where state='active' and ending_tsz <= now() - '1 day'::interval;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10815555.72..10815555.73 rows=1 width=0) (actual time=1416200.548..1416200.548 rows=1 loops=1)
-> Seq Scan on objects (cost=0.00..10795673.36 rows=7952943 width=0) (actual time=1210074.356..1416200.498 rows=13 loops=1)
Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 day'::interval)))
Total runtime: 1416200.678 ms
(4 rows)

version with enabled bitmap scans is still working on (3 hours now), will post explain analyze when it finishes.

explain (with enabled bitmap scans) looks like this:

$ explain select count(*) from objects where state='active' and ending_tsz <= now() - '1 day'::interval;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9117480.50..9117480.51 rows=1 width=0)
-> Bitmap Heap Scan on objects (cost=326375.21..9097597.40 rows=7953240 width=0)
Recheck Cond: (state = 'active'::text)
Filter: (ending_tsz <= (now() - '1 day'::interval))
-> Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..324386.90 rows=9754574 width=0)
Index Cond: (state = 'active'::text)
(6 rows)

value of state and ending_tsz are pretty correlated - it's very
unlikely to have rows matching both criteria (state = 'active' and
ending_tsz <= now() - '1 day').

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/