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 C311D3A45C5 for ; Tue, 30 Nov 2004 15:37:42 +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 66098-09 for ; Tue, 30 Nov 2004 15:37:34 +0000 (GMT) Received: from stubee.d2hosting.net (d2hosting.net [66.70.41.160]) by svr1.postgresql.org (Postfix) with ESMTP id A24873A538C for ; Tue, 30 Nov 2004 15:37:36 +0000 (GMT) Received: from [127.0.0.1] (stubee.d2hosting.net [66.70.41.160]) by stubee.d2hosting.net (8.11.6/linuxconf) with ESMTP id iAUFbZj04378; Tue, 30 Nov 2004 09:37:35 -0600 Message-ID: <41AC94CC.1020602@idigx.com> Date: Tue, 30 Nov 2004 09:42:04 -0600 From: Thomas Swan User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Dmitry Karasik Cc: pgsql-performance@postgresql.org Subject: Re: VACUUM ANALYZE downgrades performance References: <84fz2ra2gy.fsf_-_@plab.ku.dk> In-Reply-To: <84fz2ra2gy.fsf_-_@plab.ku.dk> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit 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/420 X-Sequence-Number: 9374 On 11/30/2004 7:30 AM Dmitry Karasik said:: >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'. > > > Look at the ACTUAL TIME. It dropped from 0.029ms (using the index scan) to 0.009ms (using a sequential scan.) Index scans are not always faster, and the planner/optimizer knows this. VACUUM ANALYZE is best run when a large proportion of data has been updated/loaded or in the off hours to refresh the statistics on large datasets.