Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 268FF2E0052 for ; Tue, 25 Mar 2008 05:50:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63059-02 for ; Tue, 25 Mar 2008 05:50:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from hs-out-0708.google.com (hs-out-0708.google.com [64.233.178.250]) by postgresql.org (Postfix) with ESMTP id 5E54C2E0051 for ; Tue, 25 Mar 2008 05:50:31 -0300 (ADT) Received: by hs-out-0708.google.com with SMTP id x43so2846991hsb.3 for ; Tue, 25 Mar 2008 01:50:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=KSFx0GTfXR/8EELQNigYGbsFEe7VY0G6sP4IKH/xnXk=; b=VvX0x++wflTY4Os84Br96Xv08jpYVAZ0pdFbsLGXWNkLoAb7qZkmuGF5CUtg1/Kjqcajc49fDvSmVQlUsiYsUdgPnM2RrCYDU0PsRb0hkBnA0vynWu1e/6n9k1EsWv+YsjoFoRwlabJfErDpPPcQo5mngaw3ns3YGTDC2YJqIcE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Y+dJPV6aK9Eq7TECvBVQkwgbM9cjIYolj4I8Kk/fySAnSIJ0Voup4zaUe85BFcys9LTmpJcatN3ffDRIbKp/F04rx+OdBXC/pqZ1iTsEUz66IlZJABU6nyEiaKsX1Ru/k15A2Pjs+yMdsoKovFxA9fvYIWXvDcx+2yaz2UipU0I= Received: by 10.100.214.16 with SMTP id m16mr20415953ang.38.1206435030387; Tue, 25 Mar 2008 01:50:30 -0700 (PDT) Received: by 10.100.105.20 with HTTP; Tue, 25 Mar 2008 01:50:30 -0700 (PDT) Message-ID: Date: Tue, 25 Mar 2008 14:20:30 +0530 From: "sathiya psql" To: "Uwe C. Schroeder" Subject: Re: postgresql is slow with larger table even it is in RAM Cc: pgsql-admin@postgresql.org In-Reply-To: <200803250142.14715.uwe@oss4u.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1886_18497920.1206435030366" References: <200803250142.14715.uwe@oss4u.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/204 X-Sequence-Number: 28687 ------=_Part_1886_18497920.1206435030366 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > I can't really say anything about your RAM issue, but count(*) is always a > bad > idea if it can be avoided. Assuming you have a primary key on the table, > do a > count(pk). In my experience that alone cuts down up to 30% of execution > time. > > i want exact count so am doing this... But as you are saying it is not doing so, at first without primary key i had a column "id", it took the same 3 seconds to show the count, after reading some tips i created primary key on that "id", and i did select count(id) from table, and now also it is taking the same 3 seconds. ------=_Part_1886_18497920.1206435030366 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I can't really say anything about your RAM issue, but count(*) is always a bad
idea if it can be avoided. Assuming you have a primary key on the table, do a
count(pk). In my experience that alone cuts down up to 30% of execution time.

i want exact count so am doing this...

But as you are saying it is not doing so,
at first without primary key i had a column "id", it took the same 3 seconds to show the count, after reading some tips i created primary key on that "id", and i did select count(id) from table, and now also it is taking the same 3 seconds.

------=_Part_1886_18497920.1206435030366--