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 517C73A52C6 for ; Tue, 30 Nov 2004 13:30:46 +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 16403-02 for ; Tue, 30 Nov 2004 13:30:39 +0000 (GMT) Received: from plab.ku.dk (plab.ku.dk [130.225.107.20]) by svr1.postgresql.org (Postfix) with ESMTP id 4F3383A51A1 for ; Tue, 30 Nov 2004 13:30:41 +0000 (GMT) Received: from plab.ku.dk (localhost [127.0.0.1]) by plab.ku.dk (Postfix) with SMTP id 3B86B5A18C for ; Tue, 30 Nov 2004 14:30:38 +0100 (CET) Received: by plab.ku.dk (Postfix, from userid 1003) id 9A9635A18A; Tue, 30 Nov 2004 14:30:37 +0100 (CET) Mime-version: 1.0 Content-type: text/plain; charset="koi8-r" Content-transfer-encoding: 8bit Keywords: 2001334874 X-Comment-To: Josh Berkus To: pgsql-performance@postgresql.org Subject: VACUUM ANALYZE downgrades performance From: Dmitry Karasik Date: 30 Nov 2004 14:30:37 +0100 In-Reply-To: Josh Berkus's message of "Wed, 17 Nov 2004 15:23:41 -0800" Message-ID: <84fz2ra2gy.fsf_-_@plab.ku.dk> Lines: 33 X-Mailer: Gnus v5.7/Emacs 20.7 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/418 X-Sequence-Number: 9372 Hi all, On v7.4.5 I noticed downgrade in the planner, namely favoring sequential scan over index scan. The proof: create table a ( a integer); create index aidx on a(a); explain analyze select * from a where a = 0; -- Index Scan using aidx on a (cost=0.00..17.07 rows=5 width=4) (actual -- time=0.029..0.029 rows=0 loops=1) -- Index Cond: (a = 0) vacuum analyze; explain analyze select * from a where a = 0; -- Seq Scan on a (cost=0.00..0.00 rows=1 width=4) (actual time=0.009..0.009 -- rows=0 loops=1) -- Filter: (a = 0) I do realize that there might be reasons why this happens over an empty table, but what is way worse that when the table starts actually to fill, the seq scan is still there, and the index is simply not used. How that could be so ...mmm... shortsighted, and what is more important, how to avoid this? I hope the answer is not 'run vacuum analyze each 5 seconds'. -- Sincerely, Dmitry Karasik --- catpipe Systems ApS *BSD solutions, consulting, development www.catpipe.net +45 7021 0050