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.131rows=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