X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DBEA1D1B52F for ; Wed, 14 Jan 2004 18:15:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 73261-07 for ; Wed, 14 Jan 2004 14:14:34 -0400 (AST) Received: from trade-india.com (ns5.trade-india.com [66.234.10.13]) by svr1.postgresql.org (Postfix) with SMTP id 07E53D1B4BE for ; Wed, 14 Jan 2004 14:14:33 -0400 (AST) Received: (qmail 23325 invoked from network); 14 Jan 2004 18:13:19 -0000 Received: from unknown (HELO trade-india.com) (203.145.130.142) by ns5.trade-india.com with SMTP; 14 Jan 2004 18:13:19 -0000 Message-ID: <400586CA.6090208@trade-india.com> Date: Wed, 14 Jan 2004 23:43:30 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031021 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "scott.marlowe" Cc: David Shadovitz , "pgsql-performance@postgresql.org" Subject: Re: COUNT & Pagination References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------010408030001030502060305" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/145 X-Sequence-Number: 5320 This is a multi-part message in MIME format. --------------010408030001030502060305 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit scott.marlowe wrote: >On Tue, 13 Jan 2004, David Shadovitz wrote: > > > >>>We avert the subsequent execution of count(*) by passing the >>>value of count(*) as a query parameter through the link in page >>>numbers. >>> >>> >>Mallah, and others who mentioned caching the record count: >> >>Yes, I will certainly do this. I can detect whether the query's filter has >>been changed, or whether the user is merely paging through the results or >>sorting* the results. >> >>I'd love to completely eliminate the cost of the COUNT(*) query, but I guess >>that I cannot have everything. >> >>* My HTML table column headers are hyperlinks which re-execute the query, >>sorting the results by the selected column. The first click does an ASC >>sort; a second click does a DESC sort. >> >> > >another useful trick is to have your script save out the count(*) result >in a single row table with a timestamp, and every time you grab if, check >to see if x number of minutes have passed, and if so, update that row with >a count(*). > Greetings! The count(*) can get evaluated with any arbitrary combination in whre clause how do you plan to store that information ? In a typical application pagination could be required in n number of contexts . I would be interested to know more about this trick and its applicability in such situations. Offtopic: Does PostgreSQL optimise repeated execution of similar queries ie queries on same table or set of tables (in a join) with same where clause and only differing in LIMIT and OFFSET. I dont know much about MySQL, Is their "Query Cache" achieving better results in such cases? and do we have anything similar in PostgreSQL ? I think the most recently accessed tables anyways get loaded in shared buffers in PostgreSQL so that its not accessed from the disk. But is the "Query Cache" really different from this. Can anyone knowing a little better about the working of MySQLs' query cache throw some light? Regds Mallah. > You can even have a cron job do it so your own scripts don't >incur the cost of the count(*) and delay output to the user. > > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > --------------010408030001030502060305 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit scott.marlowe wrote:
On Tue, 13 Jan 2004, David Shadovitz wrote:

  
We avert the subsequent execution of count(*) by passing the
value of count(*) as a query parameter through the link in page
numbers.
      
Mallah, and others who mentioned caching the record count:

Yes, I will certainly do this.  I can detect whether the query's filter has 
been changed, or whether the user is merely paging through the results or 
sorting* the results.

I'd love to completely eliminate the cost of the COUNT(*) query, but I guess 
that I cannot have everything.

* My HTML table column headers are hyperlinks which re-execute the query, 
sorting the results by the selected column.  The first click does an ASC 
sort; a second click does a DESC sort.
    

another useful trick is to have your script save out the count(*) result 
in a single row table with a timestamp, and every time you grab if, check 
to see if x number of minutes have passed, and if so, update that row with 
a count(*). 

Greetings!

The count(*) can get evaluated with any arbitrary combination
in whre clause how do you plan to store that information ?

In a typical application pagination could be required in n number
of contexts . I would be interested to know more about this trick
and its applicability in such situations.

Offtopic:

Does PostgreSQL optimise repeated execution of similar queries ie
queries on same table or set of tables (in a join) with same where clause
 and only differing in LIMIT and OFFSET.

I dont know much about MySQL, Is their "Query Cache" achieving
better results in such cases? and do we have anything similar in
PostgreSQL ? I think  the most recently accessed tables  anyways
get loaded in shared buffers in PostgreSQL  so that its not accessed
from the disk. But is the "Query Cache" really different from this.
Can anyone knowing a little better about the working of MySQLs'
query cache throw some light?

Regds
Mallah.

 You can even have a cron job do it so your own scripts don't 
incur the cost of the count(*) and delay output to the user.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

  

--------------010408030001030502060305--