From: Craig A. James <cjames@modgraph-usa.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 07:16:51 -0800
Message-ID: <46029DE3.9030505@modgraph-usa.com> (raw)
In-Reply-To: <20070322141807.GE11402@mathom.us>
References: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
<200703221208.19358.albert@sedifa.com>
<Pine.LNX.4.64.0703221323590.1503@ismoli.solenovo.jns>
<20070322141807.GE11402@mathom.us>
Michael Stone wrote:
> On Thu, Mar 22, 2007 at 01:30:35PM +0200, ismo.tuononen@solenovo.fi wrote:
>> approximated count?????
>>
>> why? who would need it? where you can use it?
>
> Do a google query. Look at the top of the page, where it says "results N
> to M of about O". For user interfaces (which is where a lot of this
> count(*) stuff comes from) you quite likely don't care about the exact
> count...
Right on, Michael.
One of our biggest single problems is this very thing. It's not a Postgres problem specifically, but more embedded in the idea of a relational database: There are no "job status" or "rough estimate of results" or "give me part of the answer" features that are critical to many real applications.
In our case (for a variety of reasons, but this one is critical), we actually can't use Postgres indexing at all -- we wrote an entirely separate indexing system for our data, one that has the following properties:
1. It can give out "pages" of information (i.e. "rows 50-60") without
rescanning the skipped pages the way "limit/offset" would.
2. It can give accurate estimates of the total rows that will be returned.
3. It can accurately estimate the time it will take.
For our primary business-critical data, Postgres is merely a storage system, not a search system, because we have to do the "heavy lifting" in our own code. (To be fair, there is no relational database that can handle our data.)
Many or most web-based search engines face these exact problems.
Craig
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: cjames@modgraph-usa.com
Subject: Re: Performance of count(*)
In-Reply-To: <46029DE3.9030505@modgraph-usa.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