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 14D23D9CA3 for ; Wed, 26 Oct 2005 18:28:37 -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 01401-03 for ; Wed, 26 Oct 2005 21:28:32 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.205]) by svr1.postgresql.org (Postfix) with ESMTP id 9EEA9DAF83 for ; Wed, 26 Oct 2005 18:28:33 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 71so100305wra for ; Wed, 26 Oct 2005 14:28:36 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LCdOdfiySlPuhI7MN4GhmTzAvNopXieKf2X8cuxVQFHXvWycv4gDfC3aA7PHIZvMW8NjbVtQS1hReaBGZ7jbOgUdRmVNMD+YeFNj/KNksPXhHSvsqTTu/7lJn3xjS6HetjUREc/sjHaTxXgq/aE3VLvpLabvkTjM5jEQW6yx3Gk= Received: by 10.54.112.11 with SMTP id k11mr695765wrc; Wed, 26 Oct 2005 14:28:36 -0700 (PDT) Received: by 10.54.84.8 with HTTP; Wed, 26 Oct 2005 14:28:36 -0700 (PDT) Message-ID: <33c6269f0510261428s1d320fc6w5e0ea16cfa333175@mail.gmail.com> Date: Wed, 26 Oct 2005 17:28:36 -0400 From: Alex Turner To: jd@commandprompt.com Subject: Re: browsing table with 2 million records Cc: aurora , pgsql-performance@postgresql.org In-Reply-To: <1130360977.21880.23.camel@jd.commandprompt.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1130360977.21880.23.camel@jd.commandprompt.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.111 required=5 tests=[AWL=0.087, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/395 X-Sequence-Number: 15163 You could also create your own index so to speak as a table that simply contains a list of primary keys and an order value field that you can use as your offset. This can be kept in sync with the master table using triggers pretty easily. 2 million is not very much if you only have a integer pkey, and an integer order value, then you can join it against the main table. create table my_index_table ( primary_key_value int, order_val int, primary key (primary_key_value)); create index my_index_table_order_val_i on index_table (order_val); select * from main_table a, my_index_table b where b.order_val>=3D25 and b.order_val<50 and a.primary_key_id=3Db.primary_key_id If the data updates alot then this won't work as well though as the index table will require frequent updates to potentialy large number of records (although a small number of pages so it still won't be horrible). Alex Turner NetEconomist On 10/26/05, Joshua D. Drake wrote: > > > 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/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >