selects from large tables

Hi All,

We are using Postgres 7.1, on Solaris 8 - hardware is a 400mhz Netra X1,
512Mb ram, with the database on a separate partition.

Our main result tables are getting really big, and we don't want to delete
any data yet. Currently, our largest table has around 10 million rows and
is going up at a rate of around 1 million per month. The table has 13
integer, one boolean and one timestamp column. We index the table on an ID
number and the timestamp. We vacuum analyse the table every night. The
performance has steadily degraded, and the more data we try and select, the
longer the select queries take.
The queries are not complex, and do not involve any unions etc, eg:

SELECT * FROM table_name WHERE column1 = 454 AND time BETWEEN '2002-10-13
13:44:00.0' AND '2002-11-14'

SELECT count(DISTINCT id) FROM table_name WHERE column1 = 454 AND time
BETWEEN '2002-10-13 13:44:00.0' AND '2002-11-14

See various queries and explains at the end this email for more info on the
type of queries we are doing.
Most of the queries use a sequence scan - disabling this and forcing index
scan decreases performance further for those queries.

These queries are sometimes taking over 2 minutes to perform!!!! If we
reduce the table size significantly (i.e. around 1 million rows)is is
obviously faster - down to a few seconds.

We then tried the DB on a clean installation of Solaris 9, on a dual 400mhz
processor SunE250 with 2Gb ram, and 2 scsi 17gb disks. We put the database
onto the second disk. Surprisingly the performance is only 5-10% greater.
I expected far more, due to the increased power of the machine. Looking at
the os info on this machine, the IO wait is negligible as is the cpu usage.
So this machine is not working as hard as the Netra X1, though the time
taken to perform queries is not too much different.

We have tried tweaking the shared buffers and sort mem (also tweaking kernel
shared mem size), which make little difference, and in fact if we increase
it to around 25% of total memory performance degrades slightly. We have
changed from the default amount of shared buffers, to 64000 to give us
access to 25% of the total system memory.

Any ideas on how we can select data more quickly from large tables?

Other ideas we had was to split the data over multiple table by id
(resulting in several thousand tables), however this would make management
of the database in terms of keys, triggers and integrity very difficult and
messy.

I hope someone can offer some advice.

Cheers

Nikk

- Queries and explain plans

select count(*) from table_name;
NOTICE: QUERY PLAN:
Aggregate (cost=488700.65..488700.65 rows=1 width=0)
-> Seq Scan on table_name (cost=0.00..439527.12 rows=19669412 width=0)

hawkdb=# explain select count(job_id) from table_name;
NOTICE: QUERY PLAN:
Aggregate (cost=488700.65..488700.65 rows=1 width=4)
-> Seq Scan on table_name (cost=0.00..439527.12 rows=19669412 width=4)

hawkdb=# explain select * from table_name;
NOTICE: QUERY PLAN:
Seq Scan on table_name (cost=0.00..439527.12 rows=19669412 width=57)

hawkdb=# explain select count(*) from table_name where job_id = 13;
NOTICE: QUERY PLAN:
Aggregate (cost=537874.18..537874.18 rows=1 width=0)
-> Seq Scan on table_name (cost=0.00..488700.65 rows=19669412 width=0)

hawkdb=# explain select * from table_name where job_id = 13;
NOTICE: QUERY PLAN:
Seq Scan on http_result (cost=0.00..488700.65 rows=19669412 width=57)

hawkdb=# explain select * from table_name where job_id = 1;
NOTICE: QUERY PLAN:
Index Scan using http_result_pk on table_name (cost=0.00..5.01 rows=1
width=57)

hawkdb=#explain select * from table_name where time > '2002-10-10';
NOTICE: QUERY PLAN:
Seq Scan on table_name (cost=0.00..488700.65 rows=19649743 width=57)

hawkdb=# explain select * from http_result where time < '2002-10-10';
NOTICE: QUERY PLAN:
Index Scan using table_name_time on table_name (cost=0.00..75879.17
rows=19669 width=57)

Nikk Anderson

Parallel ltd.
Cranfield Innovation Centre
University Way
Cranfield
Bedfordshire
MK43 0BT

http://www.nexuswatch.com
http://www.parallel.ltd.uk

Tel: +44 (0)8700 PARALLEL (727255)
Fax: +44 (0)8700 PARAFAX (727232)

******************************************************************
Privileged/Confidential Information may be contained in this
message. If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person), you
may not copy or deliver this message to anyone. In such case, you
should destroy this message and kindly notify the sender by reply
email. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not
relate to the official business of Parallel shall be understood
as neither given nor endorsed by it.

Unless agreed otherwise by way of a signed agreement, any business
conducted by Parallel shall be subject to its Standard Terms
and Conditions which are available upon request.
******************************************************************