Re: count * performance issue

Mark Mielke <mark@xxxxxxxxxxxxxx> · Thu, 06 Mar 2008 08:10:35 -0500

A. Kretschmer wrote:

  am  Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:

    TRIGGER i can use if i want the count of the whole table, but i require for
some of the rows with WHERE condition....

so how to do that ???

Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
count(*) from <your_table> WHERE <your_row> = ... ?

Actually - in this case, TRIGGER can be a good idea. If your count
table can include the where information, then you no longer require an
effective table-wide lock for updates.

In the past I have used sequential articles numbers within a topic for
an online community. Each topic row had an article_count. To generate a
new article, I could update the article_count and use the number I had
generated as the article number. To query the number of articles in a
particular topic, article_count was available. Given thousands of
topics, and 10s of thousands of articles, the system worked pretty
good. Not in the millions range as the original poster, but I saw no
reason why this wouldn't scale.

For the original poster: You might be desperate and looking for help
from the only place you know to get it from, but some of your recent
answers have shown that you are either not reading the helpful
responses provided to you, or you are unwilling to do your own
research. If that continues, I won't be posting to aid you.

Cheers,

mark

-- 
Mark Mielke <mark@xxxxxxxxx>