From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: aurora <aurora00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: browsing table with 2 million records
Date: Thu, 27 Oct 2005 09:43:24 +0800
Message-ID: <436030BC.1000404@familyhealth.com.au> (raw)
In-Reply-To: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
References: <cbd177510510261341l4ed7a214lda9d67af12f2ec21@mail.gmail.com>
> 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
Heh, sounds like phpPgAdmin...I really should do something about that.
> Tables seems properly indexed, with vacuum and analyze ran regularly.
> Still this very basic SQLs takes up to a minute run.
Yes, COUNT(*) on a large table is always slow in PostgreSQL. Search the
mailing lists for countless discussions about it.
Chris
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chriskl@familyhealth.com.au, aurora00@gmail.com
Subject: Re: browsing table with 2 million records
In-Reply-To: <436030BC.1000404@familyhealth.com.au>
* 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