Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51089=pgsql-performance-ar@postgresql.org>)
 id 1W3iXf-0005me-1j
 for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jan 2014 08:43:11 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51089=pgsql-performance-ar@postgresql.org>)
 id 1W3iXe-0001Ox-IL
 for pgsql-performance@arkaria.postgresql.org; Thu, 16 Jan 2014 08:43:10 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <gc@mnc.ch>) id 1W3iXd-0001Oq-Bm
 for pgsql-performance@postgresql.org; Thu, 16 Jan 2014 08:43:09 +0000
Received: from 62-2-77-205.static.cablecom.ch ([62.2.77.205] helo=mail.op.lan)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <gc@mnc.ch>) id 1W3iXZ-0002Nx-S5
 for pgsql-performance@postgresql.org; Thu, 16 Jan 2014 08:43:08 +0000
Received: from mail.mnc.ch (localhost [127.0.0.1])
 by mail.op.lan (Postfix) with ESMTP id 99285199616;
 Thu, 16 Jan 2014 09:43:00 +0100 (CET)
Received: from localhost (localhost [127.0.0.1])
 by mail.op.lan (Postfix) with ESMTP id 1933819961E;
 Thu, 16 Jan 2014 09:43:00 +0100 (CET)
X-Virus-Scanned: amavisd-new at example.com
Received: from mail.op.lan ([127.0.0.1])
 by localhost (mail.op.lan [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id 7kgfIbh5lbcN; Thu, 16 Jan 2014 09:42:59 +0100 (CET)
Received: from lmeuh2.localdomain (13-122.193-178.cust.bluewin.ch
 [178.193.122.13]) (Authenticated sender: gc@mnc.ch)
 by mail.op.lan (Postfix) with ESMTPSA id F355B199616;
 Thu, 16 Jan 2014 09:42:58 +0100 (CET)
Received: by lmeuh2.localdomain (Postfix, from userid 500)
 id E217D280163; Thu, 16 Jan 2014 09:42:57 +0100 (CET)
From: Guillaume Cottenceau <gc@mnc.ch>
To: Kai Sellgren <kaisellgren@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow counting on v9.3
References: 
 <CANyR-3ecSwJN9eOLtOLictrnAp0JomF+ZKO-xBVQRtFhzn5gWw@mail.gmail.com>
Date: Thu, 16 Jan 2014 09:42:57 +0100
In-Reply-To: 
 <CANyR-3ecSwJN9eOLtOLictrnAp0JomF+ZKO-xBVQRtFhzn5gWw@mail.gmail.com>
 (Kai Sellgren's message of "Mon, 13 Jan 2014 23:57:20 +0200")
Message-ID: <m3sisojp0u.fsf@mnc.ch>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -0.2 (/)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Kai Sellgren <kaisellgren 'at' gmail.com> writes:

> Hi,
>
> I'm experiecing slow count performance:
>
> SELECT COUNT(*) AS "count"
> FROM "NewsArticle"
>
> Takes 210 ms. I've run analyze and vacuum. I'm on 9.3. Here're the stats=
=A0http:/
> /d.pr/i/6YoB
>
> I don't understand why is it that slow. It returns 1 integer, and counts
> without filters.

You might actually have a lot more dead tuples than reported in
statistic. Last vacuum is old according to your screenshot. Try
"VACUUM FULL ANALYZE" on your table, then try again counting.


> This performs quickly:
>
> SELECT reltuples AS count
> FROM pg_class
> WHERE relname =3D 'NewsArticle';

This is not the same. This one uses precomputed statistics, and
doesn't scan the actual table data.


> But I'd like to add conditions so I don't like the last method.
>
>
> --
> Yours sincerely,
> Kai Sellgren

--=20
Guillaume Cottenceau


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance