[pgsql-jp: 28200] select count(*) performance

[Thuy Nguyen]

Hi,

I am writting a web mailer and I have a proble with its performance.

I can get data quickly by using "limit" command.  However, to 
calculate the total page number, I need to excute "select count(*) .."
and it takes more than one minute.  I really need to know record number
so that I can calculate total page number and make a link to the pages.

I am using PostgresSQL 7.2, and the following table has about 70,000 records.
I am not sure about table size because I do not know how to find it....
I created indexes for 
    MessageID, ReferencesID, FromAddr, ToAddr, Sent, Received, Created.
I know this is too many, but these fields are needed to do searching.

Please give me an advice how to speed up my application.


--------------------------------------------------------
create table tMailContentsF (
     ContentsCD  int8 not null,
         primary key (ContentsCD),
     AccountCD  int8 not null,
        CONSTRAINT tMailContentsF_AccountCD
        FOREIGN KEY(AccountCD)
        REFERENCES tMailAccountM(AccountCD)
        ON UPDATE CASCADE,
    FolderCD  int8,
        CONSTRAINT tMailContentsF_FolderCD
        FOREIGN KEY(FolderCD)
        REFERENCES tMailFolderF(FolderCD)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    MessageID  text,
    ReferencesID text,
    FromAddr  text, 
    FromName  text,
    ToAddr   text, 
    Sent   timestamp,
    Received  timestamp,
    Header   text,
    Subject   text,
    Contents  text,
    :
    :
    DeletedFlg bool
);

--------------------------------------------------------
Here is the result of EXPLAIN

# explain select contentscd,foldercd,accountcd from tMailContentsF
    where DeletedFlg = false limit 50, 0;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..63.40 rows=50 width=24)
  ->  Seq Scan on tmailcontentsf (cost=0.00..69787.99 rows=55039 width=24)

# explain select count(*) from tMailContentsF where DeletedFlg = false;
NOTICE:  QUERY PLAN:

Aggregate  (cost=69925.59..69925.59 rows=1 width=0)
  ->  Seq Scan on tmailcontentsf a  (cost=0.00..69787.99 rows=55039 width=0)