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

[Makoto Komatsu]

Hello, Thuy.

I supposed that it should be using count() with some indexes.
So if you'd like to know the total page number,
you may calculate it by application logic.

You'd write like this.

select count(ContentsCD) from tMailContentsF where AccountCD=_AccountCD_
 and FolderCD=_FolderCD_;

Could it go well? If it isn't going well, I apologize for...
Thank you.

--
Makoto Komatsu
MediaFront Inc.
 

At Thu, 5 Dec 2002 22:32:34 +0900, Thuy Nguyen
 wrote on:<002d01c29c62$c55b0580$0200a8c0 ＠ babochan>

> 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)