2011/8/8 hubert depesz lubaczewski <depesz@xxxxxxxxxx>: > Hi, > we have 8.3.11 installation on client site, with table, which looks like > this: > > $ \d objects > Table "public.objects" > Column | Type | Modifiers > -------------------------+--------------------------+--------------------------------------------------------------- > ... > state | text | > ... > ending_tsz | timestamp with time zone | default (now() + '4 mons'::interval) > ... > Indexes: > "objects_stat_user_id_creation_tsz" btree (state, user_id, creation_tsz) > "objects_ending_tsz_active" btree (ending_tsz) WHERE state = 'active'::text > "objects_ending_tsz_idx" btree (ending_tsz) > ... > > > and we have a query: > select count(*) from objects where state='active'::text and ending_tsz <= ( select now() - '1 day'::interval ); > > Normally this query has been getting plan, using > objects_ending_tsz_active, which is sane and fast. > > But today, without any sensible reason, it switched to: > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=6719810.62..6719810.63 rows=1 width=0) > InitPlan > -> Result (cost=0.00..0.01 rows=1 width=0) > -> Bitmap Heap Scan on objects (cost=1289719.58..6711422.56 rows=3355219 width=0) > Recheck Cond: ((state = 'active'::text) AND (ending_tsz <= $0)) > -> BitmapAnd (cost=1289719.58..1289719.58 rows=3355219 width=0) > -> Bitmap Index Scan on objects_stat_user_id_creation_tsz (cost=0.00..334318.95 rows=10065657 width=0) > Index Cond: (state = 'active'::text) > -> Bitmap Index Scan on objects_ending_tsz_idx (cost=0.00..953722.77 rows=24986738 width=0) > Index Cond: (ending_tsz <= $0) > (10 rows) > > running analyze objects 2 times in a row fixed the issue, but hour later > - the problem came back. > > what can be wrong? The plan turn bad without any new ANALYZE, 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 ? -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
