Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C47049F9B05 for ; Mon, 18 Dec 2006 13:08:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 49092-10 for ; Mon, 18 Dec 2006 13:08:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 310D39F9357 for ; Mon, 18 Dec 2006 13:08:02 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBIH7wCC010441; Mon, 18 Dec 2006 12:08:00 -0500 (EST) To: "Harry Hehl" cc: pgsql-performance@postgresql.org Subject: Re: Query plan changing when queried data does not In-reply-to: <6AD4F3A63B017C4FB074E2C895AD1854E31A60@EXCHSRV.waterloonetworking.net> References: <6AD4F3A63B017C4FB074E2C895AD1854E31A60@EXCHSRV.waterloonetworking.net> Comments: In-reply-to "Harry Hehl" message dated "Mon, 18 Dec 2006 11:32:19 -0500" Date: Mon, 18 Dec 2006 12:07:58 -0500 Message-ID: <10440.1166461678@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/463 X-Sequence-Number: 22519 "Harry Hehl" writes: > Why does vacuum full and reindex make a difference if the 3 tables are > never updated or records deleted? Probably because you did an ANALYZE somewhere and updated the planner's stats. I think your major problem is poor estimation of the ds_tables result: > -> Seq Scan on ds_tables (cost=0.00..59.80 rows=1 > width=48) (actual time=15.208..15.968 rows=121 loops=1) > Filter: ((lower(name) <> 'ds_omdatatest'::text) AND > (lower(name) <> 'ds_ommessage'::text) AND (lower(name) <> > 'ds_omusersetting'::text) AND (lower(name) <> 'ds_omloginsession'::text) > AND (lower(name) <> 'ds_omclassdef'::text) AND (lower(name) <> > 'ds_omuser'::text) AND (lower(name) <> 'ds_omusergroupsetting'::text) > AND (lower(name) <> 'ds_omtestobject'::text) AND (lower(name) <> > 'ds_omhomedirectory'::text) AND (lower(name) ~~ 'ds_om%'::text)) If you have an index on lower(name) then ANALYZE will collect statistics on it, and you'd get an estimate of the result size that was better than random chance ... but I bet you have no such index. You might get some improvement from raising the default statistics target, too. regards, tom lane