Thread: postgresql performance

postgresql performance

From
SPMLINGAM
Date:
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.


--
View this message in context: http://www.nabble.com/postgresql-performance-tp15847165p15847165.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


Re: postgresql performance

From
"Steinar H. Gunderson"
Date:
On Wed, Mar 05, 2008 at 02:27:08AM -0800, SPMLINGAM wrote:
>      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.

Without knowing what a "lakhs" record is, it's pretty obvious that you
haven't vacuumed in a very long time. Run VACUUM FULL on your tables, then
instate regular (non-FULL) VACUUMs or enable autovacuum.

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: postgresql performance

From
Franck Routier
Date:
Hi,

Le mercredi 05 mars 2008 à 11:39 +0100, Steinar H. Gunderson a écrit :

> Without knowing what a "lakhs" record is,

I had the same question... and Wikipedia gave me the answer : it is an
Indian word meaning 10^5, often used in indian english.

Franck



Re: postgresql performance

From
"Claus Guttesen"
Date:
>  > Without knowing what a "lakhs" record is,
>
>  I had the same question... and Wikipedia gave me the answer : it is an
>  Indian word meaning 10^5, often used in indian english.

Thank you (both OP and this post) for enlightening us with this word.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare

Re: postgresql performance

From
"Dave Dutcher"
Date:
> -----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



Re: postgresql performance

From
"Kevin Grittner"
Date:
>>> On Wed, Mar 5, 2008 at  4:39 AM, in message <20080305103945.GA3673@uio.no>,
"Steinar H. Gunderson" <sgunderson@bigfoot.com> wrote:

> it's pretty obvious that you
> haven't vacuumed in a very long time. Run VACUUM FULL on your tables

If you use VACUUM FULL, you should probably throw in ANALYZE with
it, and REINDEX, too.  An alternative that is probably faster, but
which requires that you have enough free space for a temporary
additional copy of the data, is to CLUSTER the bloated tables,
which automatically takes care of the indexes, but requires a
subsequent ANALYZE.

> regular (non-FULL) VACUUMs or enable autovacuum.

Absolutely!

-Kevin




Re: postgresql performance

From
Bill Moran
Date:
In response to "Dave Dutcher" <dave@tridecap.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.

Note that if you need a fast count of the number of rows in a large
table, there are known workarounds to get it.  Such as creating triggers
that update a count column, or using explain to get a quick estimate of
the number of rows (if that's acceptable).

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************