From: Gregory Stark <stark@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Cc: Craig James <craig_james@emolecules.com>
Subject: Re: count * performance issue
Date: Mon, 10 Mar 2008 15:16:08 +0000
Message-ID: <87r6eia9tz.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <13345.1204940295@sss.pgh.pa.us>
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
<47D00DB2.7040007@emolecules.com>
<18451.1204824562@sss.pgh.pa.us>
<200803071727.36146.josh@agliodbs.com>
<13345.1204940295@sss.pgh.pa.us>
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Well, scanning an index to get a count might be significantly faster
> than scanning the main table, but it's hardly "instantaneous". It's
> still going to take time proportional to the table size.
Hm, Mark's comment about bitmap indexes makes that not entirely true. A bitmap
index can do RLE compression which makes the relationship between the size of
the table and the time taken to scan the index more complex. In the degenerate
case where there are no concurrent updates (assuming you can determine that
quickly) it might actually be constant time.
> Unless they keep a central counter of the number of index entries;
> which would have all the same serialization penalties we've talked
> about before...
Bitmap indexes do in fact have concurrency issues -- arguably they're just a
baroque version of this central counter in this case.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
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: stark@enterprisedb.com, tgl@sss.pgh.pa.us, josh@agliodbs.com, craig_james@emolecules.com
Subject: Re: count * performance issue
In-Reply-To: <87r6eia9tz.fsf@oxford.xeocode.com>
* 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