X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 871F43A3C24 for ; Fri, 3 Dec 2004 10:42:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33816-02 for ; Fri, 3 Dec 2004 10:42:14 +0000 (GMT) Received: from plab.ku.dk (plab.ku.dk [130.225.107.20]) by svr1.postgresql.org (Postfix) with ESMTP id 0BB963A4F5B for ; Fri, 3 Dec 2004 10:42:16 +0000 (GMT) Received: from plab.ku.dk (localhost [127.0.0.1]) by plab.ku.dk (Postfix) with SMTP id F24FD5A4C2 for ; Fri, 3 Dec 2004 11:42:14 +0100 (CET) Received: by plab.ku.dk (Postfix, from userid 1003) id 22CF05A4BF; Fri, 3 Dec 2004 11:42:14 +0100 (CET) Mime-version: 1.0 Content-type: text/plain; charset="koi8-r" Content-transfer-encoding: 8bit Keywords: 2001334874 X-Comment-To: Rod Taylor To: Postgresql Performance Subject: Re: VACUUM ANALYZE downgrades performance References: <84fz2ra2gy.fsf_-_@plab.ku.dk> <41AC94CC.1020602@idigx.com> <84wtw0d6q2.fsf@plab.ku.dk> <1102004718.99116.220.camel@home> From: Dmitry Karasik In-Reply-To: Rod Taylor's message of "Thu, 02 Dec 2004 11:25:18 -0500" Date: 03 Dec 2004 11:42:13 +0100 Message-ID: <84k6rz64u2.fsf@plab.ku.dk> Lines: 29 X-Mailer: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/85 X-Sequence-Number: 9460 Hi Rod! Thomas> Index scans are not always faster, and the planner/optimizer knows Thomas> this. VACUUM ANALYZE is best run when a large proportion of data Thomas> has been updated/loaded or in the off hours to refresh the Thomas> statistics on large datasets. >> While I agree that generally this is true, look how stupid this >> behavior looks in this particular case: A developer creates a table and >> index, knowing that the table will be large and will be intensively >> used. An admin runs 'VACUUM ANALYZE' when table is occasionally empty, >> and next, say, 1 day, until another 'VACUUM ANALYZE' starts, the index >> is simply not used! Sure you don't suppose to run 'VACUUM ANALYZE' >> every 5 minutes as a solution, right? Rod> You might want to try this on the next 8.0 beta to come out, or Rod> against CVS. Tom recently applied some changes which should mitigate Rod> this situation. But this would affect only VACUUM, and not ANALYZE, right? -- Sincerely, Dmitry Karasik --- catpipe Systems ApS *BSD solutions, consulting, development www.catpipe.net +45 7021 0050