From: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com> |
---|---|
To: | aurora <aurora00(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: browsing table with 2 million records |
Date: | 2005-10-26 21:09:37 |
Message-ID: | 1130360977.21880.23.camel@jd.commandprompt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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/
From | Date | Subject | |
---|---|---|---|
Next Message | aurora | 2005-10-26 21:22:58 | Re: browsing table with 2 million records |
Previous Message | Scott Marlowe | 2005-10-26 21:06:38 | Re: browsing table with 2 million records |