Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id DDB192E2DF9 for ; Wed, 5 Mar 2008 05:57:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24746-03-2 for ; Wed, 5 Mar 2008 05:57:24 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by postgresql.org (Postfix) with ESMTP id 3BA182E2DCB for ; Wed, 5 Mar 2008 05:57:27 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so1765876waf.5 for ; Wed, 05 Mar 2008 01:57:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=CSZDT8BewwNIexVTRM+ktgu8SHUwapRsMI+AtxtnYhI=; b=W1QSacj/GeDEdL2OrabUrnEPY46ZRNDZBB9EcRpQCu0T3AI1tfp1n0kX9c+cFSyN9ekylLAztNLKxJYQd2cpCd5/KkpSPjy929jOwxbokU/AtKD1XQ0ehCHPkEaoc/GP9yyPHAFCXLViWNabQeexGmFn6rQwBvvcIbnfozDCPs0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=GNhFDhpvt5UyAxKOewV5hdvyLB1bAUisoJQoR99hDZrkgduP51gx/Lefkj4eB4uY0N891BFfdCpSxs3RHxJpXJIXi2myOsP8/x5J53uxvNEcGjm4dXgqPLcu1eCbx0mYj8Av9tgR65EUNd4Cj5k61rbJME4b7JYXvk3Q3cjZ3ic= Received: by 10.114.67.2 with SMTP id p2mr4053596waa.1.1204711046717; Wed, 05 Mar 2008 01:57:26 -0800 (PST) Received: by 10.114.208.18 with HTTP; Wed, 5 Mar 2008 01:57:26 -0800 (PST) Message-ID: Date: Wed, 5 Mar 2008 15:27:26 +0530 From: "petchimuthu lingam" To: pgsql-admin@postgresql.org Subject: Indexing + performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_19453_21441759.1204711046698" 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/36 X-Sequence-Number: 28519 ------=_Part_19453_21441759.1204711046698 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Dear Friends, I have a table with 50 lakhs records, the table has more then 10 fields, i have primary key, i have select query with count(*) without any condition, it takes 17 seconds. I have another one query which will do joins with other small tables, it takes 47 seconds to give output, the result has 2 lakhs records. the indexing is not used. I have created one index with one field ( which i used in this query, the field value has duplicates also ). Now the cpu usage is 4.4%, and waiting for io process . i have checked the above in the top command. If i delete that index, then it works fine, now the cpu usage takes more then 70%, and waiting shows less then 30%. Can Anyone explain, Why postgres behaves like this. Thanks.... -- With Best Regards, Petchimuthulingam S ------=_Part_19453_21441759.1204711046698 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Dear Friends,
     I have a table with 50 lakhs records, the table has more then 10 fields, i have primary key, i have select query with count(*) without any condition, it takes 17 seconds.

  I have another one query which will do joins with other small tables, it takes 47 seconds to give output, the result has 2 lakhs records. the indexing is not used.  I have created one index with one field ( which i used in this query, the field value has duplicates also ).

 Now the cpu usage is 4.4%, and waiting for io process .

 i have checked the above in the top command.

 If i delete that index, then it works fine, now the cpu usage takes more then 70%, and waiting shows less then 30%.

Can Anyone explain, Why postgres behaves like this.


Thanks....
     


--
With Best Regards,
Petchimuthulingam S ------=_Part_19453_21441759.1204711046698--