From: Mark Mielke <mark@mark.mielke.cc>
To: A. Kretschmer <andreas.kretschmer@schollglas.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: count * performance issue
Date: Thu, 06 Mar 2008 08:10:35 -0500
Message-ID: <47CFED4B.70806@mark.mielke.cc> (raw)
In-Reply-To: <20080306065750.GD21084@a-kretschmer.de>
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
<f966c2ee0803052143k4570d771o6c300647d3b987d@mail.gmail.com>
<20080306060829.GA21084@a-kretschmer.de>
<bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
<47CF8EA6.8070503@mark.mielke.cc>
<20080306063643.GB21084@a-kretschmer.de>
<f966c2ee0803052247n783dae24i9058d148bd17385e@mail.gmail.com>
<20080306065750.GD21084@a-kretschmer.de>
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@mielke.cc>
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: mark@mark.mielke.cc, andreas.kretschmer@schollglas.com
Subject: Re: count * performance issue
In-Reply-To: <47CFED4B.70806@mark.mielke.cc>
* 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