help tuning queries on large database

Howdy.

I'm running into scaling problems when testing with a 16gb (data
+indexes) database.

I can run a query, and it returns in a few seconds. If I run it
again, it returns in a few milliseconds. I realize this is because
during subsequent runs, the necessary disk pages have been cached by
the OS.

I have experimented with having all 8 disks in a single RAID0 set, a
single RAID10 set, and currently 4 RAID0 sets of 2 disks each. There
hasn't been an appreciable difference in the overall performance of
my test suite (which randomly generates queries like the samples
below as well as a few other types. this problem manifests itself on
other queries in the test suite as well).

So, my question is, is there anything I can do to boost performance
with what I've got, or am I in a position where the only 'fix' is
more faster disks? I can't think of any schema/index changes that
would help, since everything looks pretty optimal from the 'explain
analyze' output. I'd like to get a 10x improvement when querying from
the 'cold' state.

Thanks for any assistance. The advice from reading this list to
getting to where I am now has been invaluable.
-peter

Configuration:

PostgreSQL 8.1.1

shared_buffers = 10000 # (It was higher, 50k, but didn't help any,
so brought down to free ram for disk cache)
work_mem = 8196
random_page_cost = 3
effective_cache_size = 250000

Hardware:

CentOS 4.2 (Linux 2.6.9-22.0.1.ELsmp)
Areca ARC-1220 8-port PCI-E controller
8 x Hitachi Deskstar 7K80 (SATA2) (7200rpm)
2 x Opteron 242 @ 1.6ghz
3gb RAM (should be 4gb, but separate Linux issue preventing us from
getting it to see all of it)
Tyan Thunder K8WE

RAID Layout:

4 2-disk RAID0 sets created

Each raid set is a tablespace, formatted ext3. The majority of the
database is in the primary tablespace, and the popular object_data
table is in its own tablespace.

Sample 1:

triple_store=# explain analyze SELECT DISTINCT O.subject AS oid FROM
object_data O, object_tags T1, tags T2 WHERE O.type = 179 AND
O.subject = T1.object_id AND T1.tag_id = T2.tag_id AND T2.tag =
'transmitter\'s' LIMIT 1000;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------
Limit (cost=1245.07..1245.55 rows=97 width=4) (actual
time=3702.697..3704.665 rows=206 loops=1)
-> Unique (cost=1245.07..1245.55 rows=97 width=4) (actual
time=3702.691..3703.900 rows=206 loops=1)
-> Sort (cost=1245.07..1245.31 rows=97 width=4) (actual
time=3702.686..3703.056 rows=206 loops=1)
Sort Key: o.subject
-> Nested Loop (cost=2.82..1241.87 rows=97 width=4)
(actual time=97.166..3701.970 rows=206 loops=1)
-> Nested Loop (cost=2.82..678.57 rows=186
width=4) (actual time=59.903..1213.170 rows=446 loops=1)
-> Index Scan using tags_tag_key on tags
t2 (cost=0.00..5.01 rows=1 width=4) (actual time=13.139..13.143
rows=1 loops=1)
Index Cond: (tag =
'transmitter''s'::text)
-> Bitmap Heap Scan on object_tags t1
(cost=2.82..670.65 rows=233 width=8) (actual time=46.751..1198.198
rows=446 loops=1)
Recheck Cond: (t1.tag_id =
"outer".tag_id)
-> Bitmap Index Scan on
object_tags_tag_id_object_id (cost=0.00..2.82 rows=233 width=0)
(actual time=31.571..31.571 rows=446 loops=1)
Index Cond: (t1.tag_id =
"outer".tag_id)
-> Index Scan using object_data_pkey on
object_data o (cost=0.00..3.02 rows=1 width=4) (actual
time=5.573..5.574 rows=0 loops=446)
Index Cond: (o.subject = "outer".object_id)
Filter: ("type" = 179)
Total runtime: 3705.166 ms
(16 rows)

triple_store=# explain analyze SELECT DISTINCT O.subject AS oid FROM
object_data O, object_tags T1, tags T2 WHERE O.type = 179 AND
O.subject = T1.object_id AND T1.tag_id = T2.tag_id AND T2.tag =
'transmitter\'s' LIMIT 1000;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------
Limit (cost=1245.07..1245.55 rows=97 width=4) (actual
time=11.037..12.923 rows=206 loops=1)
-> Unique (cost=1245.07..1245.55 rows=97 width=4) (actual
time=11.031..12.190 rows=206 loops=1)
-> Sort (cost=1245.07..1245.31 rows=97 width=4) (actual
time=11.027..11.396 rows=206 loops=1)
Sort Key: o.subject
-> Nested Loop (cost=2.82..1241.87 rows=97 width=4)
(actual time=0.430..10.461 rows=206 loops=1)
-> Nested Loop (cost=2.82..678.57 rows=186
width=4) (actual time=0.381..3.479 rows=446 loops=1)
-> Index Scan using tags_tag_key on tags
t2 (cost=0.00..5.01 rows=1 width=4) (actual time=0.058..0.061 rows=1
loops=1)
Index Cond: (tag =
'transmitter''s'::text)
-> Bitmap Heap Scan on object_tags t1
(cost=2.82..670.65 rows=233 width=8) (actual time=0.310..1.730
rows=446 loops=1)
Recheck Cond: (t1.tag_id =
"outer".tag_id)
-> Bitmap Index Scan on
object_tags_tag_id_object_id (cost=0.00..2.82 rows=233 width=0)
(actual time=0.199..0.199 rows=446 loops=1)
Index Cond: (t1.tag_id =
"outer".tag_id)
-> Index Scan using object_data_pkey on
object_data o (cost=0.00..3.02 rows=1 width=4) (actual
time=0.009..0.010 rows=0 loops=446)
Index Cond: (o.subject = "outer".object_id)
Filter: ("type" = 179)
Total runtime: 13.411 ms
(16 rows)

triple_store=#

Sample 2:

triple_store=# explain analyze SELECT DISTINCT O.subject AS oid FROM
object_data O, object_tags T1, tags T2 WHERE O.type = 93 AND
O.subject = T1.object_id AND T1.tag_id = T2.tag_id AND T2.tag =
'current' LIMIT 1000;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------
Limit (cost=1241.88..1241.88 rows=1 width=4) (actual
time=6411.409..6411.409 rows=0 loops=1)
-> Unique (cost=1241.88..1241.88 rows=1 width=4) (actual
time=6411.405..6411.405 rows=0 loops=1)
-> Sort (cost=1241.88..1241.88 rows=1 width=4) (actual
time=6411.400..6411.400 rows=0 loops=1)
Sort Key: o.subject
-> Nested Loop (cost=2.82..1241.87 rows=1 width=4)
(actual time=6411.386..6411.386 rows=0 loops=1)
-> Nested Loop (cost=2.82..678.57 rows=186
width=4) (actual time=46.045..2229.978 rows=446 loops=1)
-> Index Scan using tags_tag_key on tags
t2 (cost=0.00..5.01 rows=1 width=4) (actual time=11.798..11.802
rows=1 loops=1)
Index Cond: (tag = 'current'::text)
-> Bitmap Heap Scan on object_tags t1
(cost=2.82..670.65 rows=233 width=8) (actual time=34.222..2216.321
rows=446 loops=1)
Recheck Cond: (t1.tag_id =
"outer".tag_id)
-> Bitmap Index Scan on
object_tags_tag_id_object_id (cost=0.00..2.82 rows=233 width=0)
(actual time=25.523..25.523 rows=446 loops=1)
Index Cond: (t1.tag_id =
"outer".tag_id)
-> Index Scan using object_data_pkey on
object_data o (cost=0.00..3.02 rows=1 width=4) (actual
time=9.370..9.370 rows=0 loops=446)
Index Cond: (o.subject = "outer".object_id)
Filter: ("type" = 93)
Total runtime: 6411.516 ms
(16 rows)

triple_store=# explain analyze SELECT DISTINCT O.subject AS oid FROM
object_data O, object_tags T1, tags T2 WHERE O.type = 93 AND
O.subject = T1.object_id AND T1.tag_id = T2.tag_id AND T2.tag =
'current' LIMIT 1000;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------
Limit (cost=1241.88..1241.88 rows=1 width=4) (actual
time=9.437..9.437 rows=0 loops=1)
-> Unique (cost=1241.88..1241.88 rows=1 width=4) (actual
time=9.431..9.431 rows=0 loops=1)
-> Sort (cost=1241.88..1241.88 rows=1 width=4) (actual
time=9.426..9.426 rows=0 loops=1)
Sort Key: o.subject
-> Nested Loop (cost=2.82..1241.87 rows=1 width=4)
(actual time=9.414..9.414 rows=0 loops=1)
-> Nested Loop (cost=2.82..678.57 rows=186
width=4) (actual time=0.347..3.477 rows=446 loops=1)
-> Index Scan using tags_tag_key on tags
t2 (cost=0.00..5.01 rows=1 width=4) (actual time=0.039..0.042 rows=1
loops=1)
Index Cond: (tag = 'current'::text)
-> Bitmap Heap Scan on object_tags t1
(cost=2.82..670.65 rows=233 width=8) (actual time=0.297..1.688
rows=446 loops=1)
Recheck Cond: (t1.tag_id =
"outer".tag_id)
-> Bitmap Index Scan on
object_tags_tag_id_object_id (cost=0.00..2.82 rows=233 width=0)
(actual time=0.185..0.185 rows=446 loops=1)
Index Cond: (t1.tag_id =
"outer".tag_id)
-> Index Scan using object_data_pkey on
object_data o (cost=0.00..3.02 rows=1 width=4) (actual
time=0.009..0.009 rows=0 loops=446)
Index Cond: (o.subject = "outer".object_id)
Filter: ("type" = 93)
Total runtime: 9.538 ms
(16 rows)

triple_store=#

Schema:

triple_store=# \d object_data
Table "public.object_data"
Column | Type | Modifiers
---------------+-----------------------------+-----------
subject | integer | not null
type | integer | not null
owned_by | integer | not null
created_by | integer | not null
created | timestamp without time zone | not null
last_modified | timestamp without time zone | not null
label | text |
Indexes:
"object_data_pkey" PRIMARY KEY, btree (subject)
"object_data_type_created_by" btree ("type", created_by)
"object_data_type_owned_by" btree ("type", owned_by)
Foreign-key constraints:
"object_data_created_by_fkey" FOREIGN KEY (created_by)
REFERENCES objects(object_id) DEFERRABLE INITIALLY DEFERRED
"object_data_owned_by_fkey" FOREIGN KEY (owned_by) REFERENCES
objects(object_id) DEFERRABLE INITIALLY DEFERRED
"object_data_type_fkey" FOREIGN KEY ("type") REFERENCES objects
(object_id) DEFERRABLE INITIALLY DEFERRED
Tablespace: "alt_2"

triple_store=# \d object_tags
Table "public.object_tags"
Column | Type | Modifiers
-----------+---------+-----------
object_id | integer | not null
tag_id | integer | not null
Indexes:
"object_tags_pkey" PRIMARY KEY, btree (object_id, tag_id)
"object_tags_tag_id" btree (tag_id)
"object_tags_tag_id_object_id" btree (tag_id, object_id)
Foreign-key constraints:
"object_tags_object_id_fkey" FOREIGN KEY (object_id) REFERENCES
objects(object_id) DEFERRABLE INITIALLY DEFERRED
"object_tags_tag_id_fkey" FOREIGN KEY (tag_id) REFERENCES tags
(tag_id) DEFERRABLE INITIALLY DEFERRED

triple_store=# \d tags
Table "public.tags"
Column | Type | Modifiers
--------+---------
+-------------------------------------------------------
tag_id | integer | not null default nextval('tags_tag_id_seq'::regclass)
tag | text | not null
Indexes:
"tags_pkey" PRIMARY KEY, btree (tag_id)
"tags_tag_key" UNIQUE, btree (tag)

--
(peter.royal|osi)@pobox.com - http://fotap.org/~osi