From: Craig Ringer <craig@postnewspapers.com.au>
To: pgsql-performance@postgresql.org
Subject: Re: what is the maximum number of rows in a table in postgresql 8.1
Date: Tue, 25 Mar 2008 21:24:17 +0900
Message-ID: <47E8EEF1.40709@postnewspapers.com.au> (raw)
In-Reply-To: <f966c2ee0803250508t444524b6y44b9ee67c8e04673@mail.gmail.com>
References: <f966c2ee0803250442g9ceaf61v2c3f35221fcc6481@mail.gmail.com>
<7be3f35d0803250448h62f47b13mebe0252fca9b90e1@mail.gmail.com>
<f966c2ee0803250454k716edf4cgcd054e7b563e530c@mail.gmail.com>
<20080325120101.GC6756@alvh.no-ip.org>
<f966c2ee0803250508t444524b6y44b9ee67c8e04673@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au
Subject: Re: what is the maximum number of rows in a table in postgresql 8.1
In-Reply-To: <47E8EEF1.40709@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox