From: "Dave Dutcher" <dave@tridecap.com>
To: "'SPMLINGAM'" <spmlingam@gmail.com>,
	<pgsql-performance@postgresql.org>
References: <15847165.post@talk.nabble.com>
Subject: Re: postgresql performance
Date: Wed, 5 Mar 2008 08:09:39 -0600
Message-ID: <01b501c87eca$8da00ff0$8e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Ach+q71F0GwlYzhiR6SuW75mpOxO8wAHSdPQ
In-Reply-To: <15847165.post@talk.nabble.com>

> -----Original Message-----
> From: SPMLINGAM
> Subject: [PERFORM] postgresql performance
> 
> 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.

17 seconds to scan 5 million records doesn't sound that bad to me.
Postgresql does not store a count of records, and so it has to actually scan
the table to count all the records.  This was a design choice because select
count(*) isn't usually used in a production system.  


>   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 ).

You should post which version of Postgresql you are using, your table
definition, and the output of EXPLAIN ANALYSE run on your query.  If you
have a lot of IO wait, you are most likely IO bound.  When Postgresql is
using a lot of CPU it is likely performing a sort or hashing.  Pulling a
large number of rows out of an even larger table can be difficult to do
extremely quickly, but if you post the EXPLAIN ANALYZE output we would know
if things could be improved or not.

Dave