Re: Problem with planner

On Tue, Aug 09, 2011 at 05:11:09PM +0200, Cédric Villemain wrote:
> The plan turn bad without any new ANALYZE, right ?

Right.

> does the table increase more quickly now than before ? is it now way
> larger than before ?
> Also, do you have an explain with the 'good' plan ?

changes in the objects table are more or less the same (in volume) day
to day.

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)

for comparison, normal plan, with enable_bitmapscan = true:

$ explain select count(*) from objects where state='active' and ending_tsz <= (select now() - '1 day'::interval );
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6726333.89..6726333.90 rows=1 width=0)
InitPlan
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Bitmap Heap Scan on objects (cost=1295618.40..6717973.52 rows=3344138 width=0)
Recheck Cond: ((state = 'active'::text) AND (ending_tsz <= $0))
-> BitmapAnd (cost=1295618.40..1295618.40 rows=3344138 width=0)
-> Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..333925.70 rows=10032414 width=0)
Index Cond: (state = 'active'::text)
-> Bitmap Index Scan on objects_ending_tsz_idx (cost=0.00..960020.38 rows=25015994 width=0)
Index Cond: (ending_tsz <= $0)
(10 rows)

Now. Interesting is, that if i'll change the query like Tom suggested, things go really bad:

$ set enable_bitmapscan = true;
SET
$ explain select count(*) from objects where state='active' and ending_tsz <= now() - '1 day'::interval;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=9192258.28..9192258.29 rows=1 width=0)
-> Bitmap Heap Scan on objects (cost=335966.69..9171848.45 rows=8163932 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..333925.70 rows=10032414 width=0)
Index Cond: (state = 'active'::text)
(6 rows)

$ set enable_bitmapscan = false;
SET
$ explain select count(*) from objects where state='active' and ending_tsz <= now() - '1 day'::interval;
QUERY PLAN
--------------------------------------------------------------------------------------------
Aggregate (cost=10814335.47..10814335.48 rows=1 width=0)
-> Seq Scan on objects (cost=0.00..10793925.64 rows=8163932 width=0)
Filter: ((state = 'active'::text) AND (ending_tsz <= (now() - '1 day'::interval)))
(3 rows)

Current stats of the table:

$ select * from pg_stat_user_tables where relname = 'objects';
-[ RECORD 1 ]----+------------------------------
relid | 71635994
schemaname | public
relname | objects
seq_scan | 181
seq_tup_read | 3164627085
idx_scan | 164923232565
idx_tup_fetch | 1359016133552
n_tup_ins | 31372199
n_tup_upd | 698411462
n_tup_del | 1
n_tup_hot_upd | 20426973
n_live_tup | 75016862
n_dead_tup | 494489
last_vacuum | 2011-03-31 06:15:39.866869+00
last_autovacuum | 2011-08-09 05:51:35.050683+00
last_analyze | 2011-08-09 03:30:14.986266+00
last_autoanalyze | 2010-09-27 05:10:10.793584+00

Best regards,

depesz