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 9D4CB3A537B for ; Tue, 30 Nov 2004 15:33:05 +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 63840-06 for ; Tue, 30 Nov 2004 15:32:58 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.200]) by svr1.postgresql.org (Postfix) with ESMTP id 12D2B3A5235 for ; Tue, 30 Nov 2004 15:33:01 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a36so539544rnf for ; Tue, 30 Nov 2004 07:33:01 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=Z6IPR1NCOp2sbJYIZuNcwOw99U7GYSNRPPCcTkQcd9FVNdLI2t9odNwPQl3ccmJ+z4IgLZ8j4WOpLzjwQcs9GJNcHDkROt26Bn9f/3YdnWxZ09KSdFg2Ws9K06gB5gDrayhSbR8AXWTcMG/WoaY6/yWyqt/btl6qj6RawO5Lt2E= Received: by 10.38.164.65 with SMTP id m65mr521945rne; Tue, 30 Nov 2004 07:33:01 -0800 (PST) Received: by 10.38.126.16 with HTTP; Tue, 30 Nov 2004 07:33:01 -0800 (PST) Message-ID: Date: Tue, 30 Nov 2004 10:33:01 -0500 From: Mike Rylander Reply-To: Mike Rylander To: Dmitry Karasik , pgsql-performance@postgresql.org Subject: Re: VACUUM ANALYZE downgrades performance In-Reply-To: <84fz2ra2gy.fsf_-_@plab.ku.dk> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <84fz2ra2gy.fsf_-_@plab.ku.dk> 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/419 X-Sequence-Number: 9373 On 30 Nov 2004 14:30:37 +0100, Dmitry Karasik wrote: > > 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) Looks to me like the seq scan is a better plan. The "actual time" went down. > > 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'. > See this thread (http://archives.postgresql.org/pgsql-hackers/2004-11/msg00985.php and http://archives.postgresql.org/pgsql-hackers/2004-11/msg01080.php) for an ongoing discussion of the issue. -- Mike Rylander mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org