From: Dave Dutcher <dave@tridecap.com>
To: 'SPMLINGAM' <spmlingam@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: postgresql performance
Date: Wed, 5 Mar 2008 08:09:39 -0600
Message-ID: <01b501c87eca$8da00ff0$8e00a8c0@tridecap.com> (raw)
In-Reply-To: <15847165.post@talk.nabble.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, spmlingam@gmail.com
Subject: Re: postgresql performance
In-Reply-To: <01b501c87eca$8da00ff0$8e00a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox