Message-ID: <47E8EEF1.40709@postnewspapers.com.au>
Date: Tue, 25 Mar 2008 21:24:17 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: what is the maximum number of rows in a table in postgresql
 8.1
References: <f966c2ee0803250442g9ceaf61v2c3f35221fcc6481@mail.gmail.com>	
	<7be3f35d0803250448h62f47b13mebe0252fca9b90e1@mail.gmail.com>	
	<f966c2ee0803250454k716edf4cgcd054e7b563e530c@mail.gmail.com>	
	<20080325120101.GC6756@alvh.no-ip.org>
	<f966c2ee0803250508t444524b6y44b9ee67c8e04673@mail.gmail.com>
In-Reply-To: <f966c2ee0803250508t444524b6y44b9ee67c8e04673@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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