X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0C39EDAFA3 for ; Wed, 26 Oct 2005 18:09:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95553-01 for ; Wed, 26 Oct 2005 21:09:30 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id BAF4DDAF9F for ; Wed, 26 Oct 2005 18:09:31 -0300 (ADT) Received: from [192.168.1.55] (fc1smp [66.93.38.87]) (authenticated bits=0) by hosting.commandprompt.com (8.13.4/8.13.4) with ESMTP id j9QL5hbM013004; Wed, 26 Oct 2005 14:05:44 -0700 Subject: Re: browsing table with 2 million records From: "Joshua D. Drake" Reply-To: jd@commandprompt.com To: aurora Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Organization: Command Prompt, Inc. Date: Wed, 26 Oct 2005 14:09:37 -0700 Message-Id: <1130360977.21880.23.camel@jd.commandprompt.com> Mime-Version: 1.0 X-Mailer: Evolution 2.4.0 (2.4.0-3.1.fc4.nr) Content-Transfer-Encoding: 7bit X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (hosting.commandprompt.com [192.168.1.101]); Wed, 26 Oct 2005 14:05:44 -0700 (PDT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 required=5 tests=[AWL=0.011] X-Spam-Level: X-Archive-Number: 200510/393 X-Sequence-Number: 15161 > We have a GUI that let user browser through the record page by page at > about 25 records a time. (Don't ask me why but we have to have this > GUI). This translates to something like > > select count(*) from table <-- to give feedback about the DB size Do you have a integer field that is an ID that increments? E.g; serial? > select * from table order by date limit 25 offset 0 You could use a cursor. Sincerely, Joshua D. Drake > > Tables seems properly indexed, with vacuum and analyze ran regularly. > Still this very basic SQLs takes up to a minute run. > > I read some recent messages that select count(*) would need a table > scan for Postgre. That's disappointing. But I can accept an > approximation if there are some way to do so. But how can I optimize > select * from table order by date limit x offset y? One minute > response time is not acceptable. > > Any help would be appriciated. > > Wy > > -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/