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 754933A5749 for ; Thu, 2 Dec 2004 16:07:34 +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 94400-05 for ; Thu, 2 Dec 2004 16:07:21 +0000 (GMT) Received: from plab.ku.dk (plab.ku.dk [130.225.107.20]) by svr1.postgresql.org (Postfix) with ESMTP id 6DE9C3A576A for ; Thu, 2 Dec 2004 16:07:20 +0000 (GMT) Received: from plab.ku.dk (localhost [127.0.0.1]) by plab.ku.dk (Postfix) with SMTP id 306A15A3C8 for ; Thu, 2 Dec 2004 17:07:18 +0100 (CET) Received: by plab.ku.dk (Postfix, from userid 1003) id B92D15A3C3; Thu, 2 Dec 2004 17:07:17 +0100 (CET) Mime-version: 1.0 Content-type: text/plain; charset="koi8-r" Content-transfer-encoding: 8bit Keywords: 2001334874 X-Comment-To: Thomas Swan To: pgsql-performance@postgresql.org Subject: Re: VACUUM ANALYZE downgrades performance References: <84fz2ra2gy.fsf_-_@plab.ku.dk> <41AC94CC.1020602@idigx.com> From: Dmitry Karasik In-Reply-To: Thomas Swan's message of "Tue, 30 Nov 2004 09:42:04 -0600" Date: 02 Dec 2004 17:07:17 +0100 Message-ID: <84wtw0d6q2.fsf@plab.ku.dk> Lines: 39 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/48 X-Sequence-Number: 9423 Hi Thomas! Thomas> Look at the ACTUAL TIME. It dropped from 0.029ms (using the index Thomas> scan) to 0.009ms (using a sequential scan.) 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? I'm not sure if there's ever such thing like planner hints, such as, "yes, we were switched from index back to seqscan, but this switch is only valid until table has less than X records", but it sounds as a reasonable solution. Well anyway, here's the scenario that cannot be fought neither by SQL programming nor by administrative guidelines, at least as I see it. And yes, I looked on the actual time, but somehow am not moved by how fast postgresql can seqscan an empty table, really. I believe there's something wrong if decisions based on a table when it is empty, are suddenly applied when it is full. -- Sincerely, Dmitry Karasik --- catpipe Systems ApS *BSD solutions, consulting, development www.catpipe.net +45 7021 0050