From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Brown <kevin@sysexperts.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
Date: Sun, 20 Apr 2003 11:21:32 -0400
Message-ID: <26961.1050852092@sss.pgh.pa.us> (raw)
In-Reply-To: <20030420062852.GK1847@filer>
References: <EKEBJNAJDPKJBDFGJNIJAENMDBAA.denis@next2me.com>
<200304090918.45275.josh@agliodbs.com>
<20030419130146.GI1847@filer>
<21955.1050767928@sss.pgh.pa.us>
<20030420011336.GJ1847@filer>
<24735.1050809664@sss.pgh.pa.us>
<20030420062852.GK1847@filer>
Kevin Brown <kevin@sysexperts.com> writes:
> This is why I suspect the best way to manage this would be to manage
> the counter itself using the MVCC mechanism (that is, you treat the
> shared counter as a row in a table just like any other and, in fact,
> it might be most beneficial for it to actually be exactly that), which
> handles the visibility problem automatically. But I don't know how
> much contention there would be as a result.
Hm. Contention probably wouldn't be the killer, since if transactions
don't try to update the count until they are about to commit, they won't
be holding the row lock for long. (You'd have to beware of deadlocks
between transactions that need to update multiple counters, but that
seems soluble.) What *would* be a problem is that such counter tables
would accumulate huge numbers of dead rows very quickly, making it
inefficient to find the live row. Josh already mentioned this as a
problem with user-trigger-based counting. You could stanch the bleeding
with sufficiently frequent vacuums, perhaps, but it just doesn't look
very appealing.
Ultimately what this comes down to is "how much overhead are we willing
to load onto all other operations in order to make SELECT-COUNT(*)-with-
no-WHERE-clause fast"? Postgres has made a set of design choices that
favor the other operations. If you've designed an application that
lives or dies by fast COUNT(*), perhaps you should choose another
database.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kevin@sysexperts.com, pgsql-performance@postgresql.org
Subject: Re: [SQL] Yet Another (Simple) Case of Index not used
In-Reply-To: <26961.1050852092@sss.pgh.pa.us>
* 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