Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Scott Marlowe" wrote: >FYI, I went to the ebay page you posted, which listed something like >98011 pages, and asked for page 96000. It searched for about a minute >and timed out with the error message > >There was a problem executing your request. Please try again. > >Tried it again, twice, abou

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > "Trevor Talbot" wrote: > > >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > > > >> My point is that whatever search criterias are involved and how many items > >> are found eBay always returns the *accurate* number of items found. > >> >

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Trevor Talbot" wrote: >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> My point is that whatever search criterias are involved and how many items >> are found eBay always returns the *accurate* number of items found. >> >> Before this drifts off: >> * I do know *why* count(*) is slow us

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > My point is that whatever search criterias are involved and how many items > are found eBay always returns the *accurate* number of items found. > > Before this drifts off: > * I do know *why* count(*) is slow using Postgres. > * I *think* tha

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Tom Lane wrote: >Rainer Bauer <[EMAIL PROTECTED]> writes: >> My point is that whatever search criterias are involved and how many items >> are found eBay always returns the *accurate* number of items found. > >And exactly how do you know that that's true? 5 years experience with developing a bro

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Bill Moran wrote: >> Consider this query with multiple WHERE conditions: >> >>

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Aug 16, 5:19 am, [EMAIL PROTECTED] (Decibel!) wrote: > On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: > > "Scott Marlowe" wrote: > > But if you go to eBay, they always give you an accurate count. Even if the > > no. > > of items found is pretty large (example:

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Rainer Bauer <[EMAIL PROTECTED]> writes: > My point is that whatever search criterias are involved and how many items > are found eBay always returns the *accurate* number of items found. And exactly how do you know that that's true? regards, tom lane ---

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

In response to Rainer Bauer <[EMAIL PROTECTED]>: > "Trevor Talbot" wrote: > > >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > > > >> >> But if you go to eBay, they always give you an accurate count. Even if > >> >> the no. > >> >> of items found is pretty large (example:

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 16/08/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > Gregory Stark wrote: > > >"Rainer Bauer" <[EMAIL PROTECTED]> writes: > > > >> Anyway, what Phoenix is trying to say is that 2 queries are required: One > >> to > >> get the total count and one to get the tuples for the current page. I > >> re

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Martijn van Oosterhout wrote: >On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote: >> Anyway, what Phoenix is trying to say is that 2 queries are required: One to >> get the total count and one to get the tuples for the current page. I reckon >> it would help, if the query returning the

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Gregory Stark wrote: >"Rainer Bauer" <[EMAIL PROTECTED]> writes: > >> Anyway, what Phoenix is trying to say is that 2 queries are required: One to >> get the total count and one to get the tuples for the current page. I reckon >> it would help, if the query returning the result set could also repo

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Trevor Talbot" wrote: >On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> >> But if you go to eBay, they always give you an accurate count. Even if >> >> the no. >> >> of items found is pretty large (example: ). >> > >> >And I'd bet money that they're using a fu

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Thu, Aug 16, 2007 at 01:09:32PM +0200, Rainer Bauer wrote: > Anyway, what Phoenix is trying to say is that 2 queries are required: One to > get the total count and one to get the tuples for the current page. I reckon > it would help, if the query returning the result set could also report the >

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/16/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > >> But if you go to eBay, they always give you an accurate count. Even if the > >> no. > >> of items found is pretty large (example: ). > > > >And I'd bet money that they're using a full text search of some kind to

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Rainer Bauer" <[EMAIL PROTECTED]> writes: > Anyway, what Phoenix is trying to say is that 2 queries are required: One to > get the total count and one to get the tuples for the current page. I reckon > it would help, if the query returning the result set could also report the > total no. of tuple

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Decibel! wrote: >On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: >> "Scott Marlowe" wrote: >> >> >When I go to amazon.com I only ever get three pages of results. ever. >> > Because they know that returning 190 pages is not that useful, as >> >hardly anyone is going to wander throug

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: > "Scott Marlowe" wrote: > > >When I go to amazon.com I only ever get three pages of results. ever. > > Because they know that returning 190 pages is not that useful, as > >hardly anyone is going to wander through that many pages. > >

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Scott Marlowe" wrote: >When I go to amazon.com I only ever get three pages of results. ever. > Because they know that returning 190 pages is not that useful, as >hardly anyone is going to wander through that many pages. > >Google, you'll notice says "Results 1 - 10 of about 5,610,000 for >blacks

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Thursday, 16 August 2007 1:24 AM To: Phoenix Kiula Cc: Gregory Stark; Postgres General Subject: Re: [GENERAL] Yet Another COUNT(*)...WHERE...question On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > On 15/08/07, Scott Marlowe <[EMAIL

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On Aug 15, 2007, at 9:36 AM, Phoenix Kiula wrote: I'm grappling with a lot of reporting code for our app that relies on queries such as: SELECT COUNT(*) FROM TABLE WHERE (conditions)... And I still do not find, from the discussions on this thread, any truly viable solution for this. T

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

In response to "Phoenix Kiula" <[EMAIL PROTECTED]>: > > Yes, optimization. :) You don't need an exact count to tell someone > > that there's more data and they can go to it. > > > In general, I agree. But my example of Amazon was only to illustrate > the point about two queries and why they may

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

> Yes, optimization. :) You don't need an exact count to tell someone > that there's more data and they can go to it. In general, I agree. But my example of Amazon was only to illustrate the point about two queries and why they may be needed. I seem to see many more pages than you do, but in any

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 15/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > > > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > > > > > > I'm grappling with a lot of reporting code for our app that relies o

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

--- Phoenix Kiula <[EMAIL PROTECTED]> wrote: > Sorry I was not clear. Imagine an Amazon.com search results page. It > has about 15 results on Page 1, then it shows "Page 1 of 190". I don't think that amazon or google really need to give an accurate count in determining an estimated number of pag

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > On 15/08/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > > On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > > > > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > > > > > > > > I'm

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > Generally, for these kinds of things it's often best to use > materialized views / rollup tables so that you aren't re-aggregating > the same data over and over. I don't know if this was already mentioned, but here is one of the links that describe t

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > > > > I'm grappling with a lot of reporting code for our app that relies on > > > queries such as: > > > > > > SELECT COUNT(*) FROM T

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

Phoenix Kiula wrote: SELECT COUNT(*) FROM TABLE WHERE (conditions)... I am not sure what the advice here is. The WHERE condition comes from the indices. So if the query was not "COUNT(*)" but just a couple of columns, the query executes in less than a second. Just that COUNT(*) become

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 8/15/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > I'm grappling with a lot of reporting code for our app that relies on > queries such as: > > SELECT COUNT(*) FROM TABLE WHERE (conditions)... > > And I still do not find, from the discussions on this thread, any > truly viable solution

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

On 15/08/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > "Phoenix Kiula" <[EMAIL PROTECTED]> writes: > > > I'm grappling with a lot of reporting code for our app that relies on > > queries such as: > > > > SELECT COUNT(*) FROM TABLE WHERE (conditions)... > >... > > The number of such possib

Re: [GENERAL] Yet Another COUNT(*)...WHERE...question

"Phoenix Kiula" <[EMAIL PROTECTED]> writes: > I'm grappling with a lot of reporting code for our app that relies on > queries such as: > > SELECT COUNT(*) FROM TABLE WHERE (conditions)... >... > The number of such possibilities for multiple WHERE conditions is > infinite... Depends on th

[GENERAL] Yet Another COUNT(*)...WHERE...question

I'm grappling with a lot of reporting code for our app that relies on queries such as: SELECT COUNT(*) FROM TABLE WHERE (conditions)... And I still do not find, from the discussions on this thread, any truly viable solution for this. The one suggestion is to have a separate counts table,