Try to change index: objects_endings_tsz_active(state,endings_tsz) where state='active'. 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? > > Best regards, > > depesz > > -- > The best thing about modern society is how easy it is to avoid contact with > it. > > http://depesz.com/ > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
