Thread: 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.
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.
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/
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
> > 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
> -----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
>>> 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
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. ****************************************************************