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 B1130DAF74 for ; Wed, 26 Oct 2005 17:41:20 -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 80370-08 for ; Wed, 26 Oct 2005 20:41:15 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.207]) by svr1.postgresql.org (Postfix) with ESMTP id B57BCD9CA3 for ; Wed, 26 Oct 2005 17:41:15 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 71so96696wra for ; Wed, 26 Oct 2005 13:41:17 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=P9WWIXB2k8aAE4KHevfPFwODZHfW6kFjyHBCRRjFrWU68M/fwF/57zwIKstsBwgb6V7xocOfAtXswKYFj+2qwomgHBOfBCfleR/JffOYjz2IHQJ5qB0tc2C/ibOK1/qBt+GGJX1gW0yciI7I+effqBMR3aQgcGzmR381GX9leHA= Received: by 10.54.80.7 with SMTP id d7mr656547wrb; Wed, 26 Oct 2005 13:41:17 -0700 (PDT) Received: by 10.54.96.18 with HTTP; Wed, 26 Oct 2005 13:41:17 -0700 (PDT) Message-ID: Date: Wed, 26 Oct 2005 13:41:17 -0700 From: aurora To: pgsql-performance@postgresql.org Subject: browsing table with 2 million records MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11717_26588224.1130359277791" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.679 required=5 tests=[FROM_ENDS_IN_NUMS=0.516, HTML_00_10=0.138, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/390 X-Sequence-Number: 15158 ------=_Part_11717_26588224.1130359277791 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I am running Postgre 7.4 on FreeBSD. The main table have 2 million record (we would like to do at least 10 mil or more). It is mainly a FIFO structur= e with maybe 200,000 new records coming in each day that displace the older records. We have a GUI that let user browser through the record page by page at abou= t 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 select * from table order by date limit 25 offset 0 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 fo= r 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 ------=_Part_11717_26588224.1130359277791 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I am running Postgre 7.4 on FreeBSD. The main table have 2 million record (we would like to do at least 10 mil or more). It is mainly a FIFO structure with maybe 200,000 new records coming in each day that displace the older records.

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
  select * from table order by date limit 25 offset 0

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


------=_Part_11717_26588224.1130359277791--