Huge Data

[Sezai YILMAZ]

Hi,

I use PostgreSQL 7.4 for storing huge amount of data. For example 7
million rows. But when I run the query "select count(*) from table;", it
results after about 120 seconds. Is this result normal for such a huge
table? Is there any methods for speed up the querying time? The huge
table has integer primary key and some other indexes for other columns.

The hardware is: PIII 800 MHz processor, 512 MB RAM, and IDE hard disk
drive.

-sezai

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Franco Bruno Borghesi]

If the mentioned solution fits your needs, you could create a stored
procedure using that. The postgreSQL implementation could select from
pg_class, while the same function in other database could execute the
select count() on the table.

On Wed, 2004-01-14 at 10:25, Sezai YILMAZ wrote:

Shridhar Daithankar wrote:

Rather than doing count(*), you should either cache the count in application
memory

or analyze often and use following.

'select reltuples from pg_class where relname = 'foo';

Thank you very much Shridhar. This one is responsive immediately. I
think I will use this method for gathering row count. But I complain to
break SQL standards. The code will become unmovable.

-sezai

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQBABUbK21d VnhLsBV0RAuyGAJ 4z6AJcbnEw9njiO rtbsFnd/a7sGwCeNeTy
G/GkE8IfE/MSNGLqGsHGoNM=
=197f
-----END PGP SIGNATURE-----

[Sezai YILMAZ]

Shridhar Daithankar wrote:

On Wednesday 14 January 2004 18:22, Matthew Lunnon wrote:

select logid, agentid, logbody from log where logid=3000000;

this query also returns after about 120 seconds. The table log has about
7 million records, and logid is the primary key of log table. What about
that? Why is it too slow?

How about

select logid, agentid, logbody from log where logid='3000000' ;

Oh my god. It is unbelievable. The result is great. Thanks for all guys
who helped me.

-sezai

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Shridhar Daithankar]

On Wednesday 14 January 2004 18:55, Sezai YILMAZ wrote:

Shridhar Daithankar wrote:

Rather than doing count(*), you should either cache the count in
application memory

or analyze often and use following.

'select reltuples from pg_class where relname = 'foo';

Thank you very much Shridhar. This one is responsive immediately. I
think I will use this method for gathering row count. But I complain to
break SQL standards. The code will become unmovable.

Well, you could document it somewhere for your reference. It is not that
hard..:-)

And remember, the value you get is just an estimate. You need to analyze the
table often with respect to it's update/insert/delete activity to keep the
estimate reasonable accurate. Vacuuming would also update the estimate.

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend