Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XkCKL-0005Lv-Ji for pgsql-admin@arkaria.postgresql.org; Fri, 31 Oct 2014 13:33:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XkCKL-0003eb-2M for pgsql-admin@arkaria.postgresql.org; Fri, 31 Oct 2014 13:33:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XkCKJ-0003ca-NC for pgsql-admin@postgresql.org; Fri, 31 Oct 2014 13:33:15 +0000 Received: from mail-pd0-x231.google.com ([2607:f8b0:400e:c02::231]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XkCKF-0001wg-Qw for pgsql-admin@postgresql.org; Fri, 31 Oct 2014 13:33:14 +0000 Received: by mail-pd0-f177.google.com with SMTP id v10so7168915pde.8 for ; Fri, 31 Oct 2014 06:33:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:message-id:date:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=Ow6yqpX7onhO2Q5g8HTcQcM6Y2CgzM6qkR9Smll0YXg=; b=qWx8T1kEngUe+P/2s3LbDYvIEHGppaPyrq/jFyS2sFQAsMQ+w8HFIiLdPBEfkt40/M 1FBX271Dfq1RzU9d/oAKUA2DfqnMxazdohcXuGnyLNXZoyYeORdjqNynA3eHuPtTOFUZ Efnw9adtxiIj6L59d30Rl/W8BxOeRpv7CI1TaTQt6Jy42et/UzIOfa+SBKxmaf/zkdHh Au7T2xN6bCwSAi4dO0mXymDlFdsLtYaKR+WRBZUtZNeo+eypXajoXVwqouegkfNYRw3d LXhCrk9+lICFV42aLHcWR5hUVC85E2tsY06wFx+Eogf8RZeBweXvv7eckKb9v7b4u1qn 7rfg== X-Received: by 10.68.243.3 with SMTP id wu3mr24672851pbc.133.1414762388559; Fri, 31 Oct 2014 06:33:08 -0700 (PDT) Received: from [192.168.11.4] ([180.164.124.36]) by mx.google.com with ESMTPSA id v9sm9962775pdm.80.2014.10.31.06.33.06 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 31 Oct 2014 06:33:07 -0700 (PDT) From: Rural Hunter X-Google-Original-From: Rural Hunter Message-ID: <54538F94.3010507@gmail.com> Date: Fri, 31 Oct 2014 21:33:08 +0800 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:31.0) Gecko/20100101 Thunderbird/31.2.0 MIME-Version: 1.0 To: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: wrong count estimation in query plan References: <5451E60A.60009@gmail.com> <19884.1414679157@sss.pgh.pa.us> In-Reply-To: <19884.1414679157@sss.pgh.pa.us> Content-Type: text/plain; charset=gb18030; format=flowed Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org ÔÚ 2014/10/30 22:25, Tom Lane дµÀ: > If ANALYZE doesn't scan the entire table then it updates the rowcount > estimate using a moving-average approach, rather than assuming that > the blocks it did look at are necessarily completely representative. > That causes the estimate to converge over multiple passes. Since it > took so many cycles to get close to reality, we can conclude that the > table is several times larger than the number of blocks ANALYZE is > willing to sample ... which, for a table with only 1000 rows in it, > says you've got a bad table-bloat problem. VACUUM FULL or CLUSTER > might be indicated. regards, tom lane Got it. A vacuum solved the problem. Thanks. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin