Re: count * performance issue

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(at)mielke(dot)cc>