From: Craig A. James <cjames@modgraph-usa.com>
To: Brian Hurt <bhurt@janestcapital.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 09:02:00 -0800
Message-ID: <4602B688.6030904@modgraph-usa.com> (raw)
In-Reply-To: <4602AA8B.3070505@janestcapital.com>
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>
<46029DE3.9030505@modgraph-usa.com>
<4602AA8B.3070505@janestcapital.com>
Brian Hurt wrote:
>> 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.
>>
> For the "give me part of the answer", I'm wondering if cursors wouldn't
> work (and if not, why not)?
There is no mechanism in Postgres (or any RDB that I know of) to say, "Give me rows 1000 through 1010", that doesn't also execute the query on rows 1-1000. In other words, the RDBMS does the work for 1010 rows, when only 10 are needed -- 100 times more work than is necessary.
Limit/Offset will return the correct 10 rows, but at the cost of doing the previous 1000 rows and discarding them.
Web applications are stateless. To use a cursor, you'd have to keep it around for hours or days, and create complex "server affinity" code to direct a user back to the same server of your server farm (where that cursor is being held), on the chance that the user will come back and ask for rows 1000 through 1010, then a cursor isn't up to the task.
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, bhurt@janestcapital.com
Subject: Re: Performance of count(*)
In-Reply-To: <4602B688.6030904@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