Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A827C2E005F for ; Thu, 27 Mar 2008 07:35:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 84139-05 for ; Thu, 27 Mar 2008 07:35:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.240]) by postgresql.org (Postfix) with ESMTP id E5E892E004D for ; Thu, 27 Mar 2008 07:35:41 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so98832ana.33 for ; Thu, 27 Mar 2008 03:35:41 -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=WymDmh1kFIco7QmmxDAomJGglHk3L3Uln0dA1xrGopI=; b=EjMDUgEK+r8vm/4di5KEYKHRvTwpl70msYe+P1FXAmtBVRj1e6cVdOjCANMoUFet+kakVUt0vaWwhdX8VuUdrGjh6iUsyftbPkBoM/h2Jz1e/w8RvMIXCyVcnA+wm448D/d3cewbUdHecGIoFHydZNkn65fZsA2j+6AlgeGIuNE= 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=esatYROzIT98i+Fh14my8g1R41wKSpthqjIc+n0hGdfGJdmdPsyjn2fx1Rodm/K6efrOaOXAGBak/T8vGmmxRhMzKpZwCPq7RYjozsPAmWky9CWJG5ErO9q+a4Qy9pFNePPiAYrrvc3W7dJ93fw/AJVdRZ0GWoqmssNvUVWz1iw= Received: by 10.100.133.20 with SMTP id g20mr3001658and.2.1206614140941; Thu, 27 Mar 2008 03:35:40 -0700 (PDT) Received: by 10.100.189.20 with HTTP; Thu, 27 Mar 2008 03:35:40 -0700 (PDT) Message-ID: <493343bd0803270335i59c2ba65yded0a1c11982b384@mail.gmail.com> Date: Fri, 28 Mar 2008 06:05:40 +1930 From: "=?ISO-8859-1?Q?Elvis_Henr=EDquez?=" To: "sathiya psql" Subject: Re: postgresql is slow with larger table even it is in RAM Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22231_24275205.1206614140930" References: <20080325094147.40317ea2@twoflower.in.publishing.hu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.876 tagged_above=0 required=5 tests=AWL=0.632, HTML_MESSAGE=0.001, INVALID_DATE_TZ_ABSURD=0.243 X-Spam-Level: X-Archive-Number: 200803/228 X-Sequence-Number: 28711 ------=_Part_22231_24275205.1206614140930 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline We could notice your processor is an Intel Celeron. They have a small cache= , as you can see for yourself in your output. But, what about postgresql.conf ? How many memory resources are being assigned in your configuration? If you only have 1.25 GB of RAM and you're creating a tablespace in RAM which holds a table about 0.8 GB in size, and have created indexes in the same tablespace in RAM, you could be getting out of RAM. vmstat says you're not using swap, but if you leave the postgresql process only ~100 MB of RAM= , it surely will have a bad performance. Once the server is up and running, which is the output of free ? Which tool are you using to retrieve data ? psql is pretty much faster than phpPgAdmin and this one is faster than pgAdmin. Elvis E. Henr=EDquez A. On Wed, Mar 26, 2008 at 4:18 AM, sathiya psql wrote: > > > On Tue, Mar 25, 2008 at 2:11 PM, CZUCZY Gergely < > gergely.czuczy@harmless.hu> wrote: > > > May I ask, do you have any indeces or a primary key on that table? > > > yes, i have both the index and primary key on a column "id", > and am doing the > > SELECT count(id) from TABLE; > > this is taking 3 seconds > ------=_Part_22231_24275205.1206614140930 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
We could notice your processor is an Intel Celeron. They have a small c= ache, as you can see for yourself in your output.

But, what about po= stgresql.conf ? How many memory resources are being assigned in your config= uration?

If you only have 1.25 GB of RAM and you're creating a tablespace in= RAM which holds a table about 0.8 GB in size, and have created indexes in = the same tablespace in RAM, you could be getting out of RAM. vmstat says yo= u're not using swap, but if you leave the postgresql process only ~100 = MB of RAM, it surely will have a bad performance.

Once the server is up and running, which is the output of free ?
Which tool are you using to retrieve data ? psql is pretty much faster tha= n phpPgAdmin and this one is faster than pgAdmin.

Elvis E. Henr=EDqu= ez A.

On Wed, Mar 26, 2008 at 4:18 AM, sathiya psq= l <sathiya.psql@gmail.com&= gt; wrote:


On Tue, Mar 25, 20= 08 at 2:11 PM, CZUCZY Gergely <gergely.czuczy@harmless.hu> wrote:
May I ask, do you have any indeces or a primary key on that table?
yes, i have both the index and primary key on a co= lumn "id",
and am doing the

SELECT count(id) from TABLE= ;

this is taking 3 seconds

------=_Part_22231_24275205.1206614140930--