Skip site navigation (1) Skip section navigation (2)

Re: what is the maximum number of rows in a table in postgresql 8.1

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: what is the maximum number of rows in a table in postgresql 8.1
Date: 2008-03-25 12:24:17
Message-ID: 47E8EEF1.40709@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
sathiya psql wrote:
>
> yes many a times i need to process all the records,
>
> often i need to use count(*) ????
>
> so what to do  ?? ( those trigger options i know already, but i wil l do
> count on different parameters )
*** PLEASE *** post the output of an EXPLAIN ANALYSE on one or more of 
your queries, and POST THE QUERY TEXT TOO. For example, if your query was:

SELECT COUNT(*) FROM sometable WHERE somefield > 42 ;

then you would run:

ANALYZE sometable;

then you would run:

EXPLAIN ANALYZE SELECT COUNT(*) FROM sometable WHERE somefield > 42 ;

and paste the resulting text into an email message to this list. Without 
your query text and the EXPLAIN ANALYZE output from it it is much harder 
for anybody to help you. You should also post the output of a psql "\d" 
command on your main table definitions.


As for what you can do to improve performance, some (hardly an exclusive 
list) of options include:


- Maintaining a summary table using a trigger. The summary table might 
track counts for various commonly-searched-for criteria. Whether this is 
practical or not depends on your queries, which you have still not 
posted to the list.

- Tuning your use of indexes (adding, removing, or adjusting indexes to 
better service your queries). Use EXPLAIN ANALYZE to help with this, and 
READ THE MANUAL, which has excellent information on tuning index use and 
profiling queries.

- Tune the query planner parameters to make better planning decisions. 
In particular, if your data and indexes all fit in ram you should reduce 
the cost of index scans relative to sequential scans. There is plenty of 
information about that on this mailing list. Also, READ THE MANUAL, 
which has excellent information on tuning the planner.

- Investigating table partitioning and tablespaces (this requires 
considerable understanding of postgresql to use successfully). You 
probably want to avoid this unless you really need it, and I doubt it 
will help much for in-memory databases anyway.

- Buy a faster computer

--
Craig Ringer

In response to

Responses

pgsql-performance by date

Next:From: sathiya psqlDate: 2008-03-25 12:50:25
Subject: Re: what is the maximum number of rows in a table in postgresql 8.1
Previous:From: Pavan DeolaseeDate: 2008-03-25 12:23:58
Subject: Re: increasing shared buffer slow downs query performance.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group