From: aurora <aurora00@gmail.com>
To: pgsql-performance@postgresql.org
Subject: browsing table with 2 million records
Date: Wed, 26 Oct 2005 13:41:17 -0700
Message-ID: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: aurora00@gmail.com
Subject: Re: browsing table with 2 million records
In-Reply-To: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
References: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox