When is a query slow?

I have a slow query (I think) that doesn't appear to be using an index for some reason. I've tried writing the query in various ways, but have so far not had any luck. Interestingly, the query plans are almost identical even when trying different variations. It appears to spend half the time scanning aw_benchmark_record_item. Is this query really slow at all?

In any case, this is only the first half of the query. I have several more joins to complete the results and ultimately perform a crosstab to determine % correct for a given set of standards associated to the benchmark.

Thanks!
Michael

Postgresql Version:
PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

OS:
Ubuntu 12.04.1 LTS (GNU/Linux 3.2.0-23-virtual x86_64)

Config:
shared_buffers = 1500MB
effective_cache_size = 3725MB
work_mem = 10MB

++++ Query ++++
explain (analyze, buffers) select
bm.rid,
t.rid,
r.fk_user,
round((avg(ri.points_received) * (100)::numeric), 0) AS percent_correct
FROM
aw_benchmark bm
join aw_benchmark_test t on (t.fk_benchmark=bm.rid)
join aw_benchmark_item bi on (bi.fk_benchmark_test=t.rid)
join aw_benchmark_record r on (r.fk_benchmark_test=t.rid)
join aw_benchmark_record_item ri on (ri.fk_benchmark_test=t.rid AND ri.fk_user=r.fk_user AND ri.fk_benchmark_item=bi.rid)
WHERE
bm.rid=11
GROUP BY 1,2,3

++++ Query Plan ++++

http://explain.depesz.com/s/kVp

HashAggregate (cost=10683.67..10683.84 rows=10 width=16) (actual time=1470.199..1475.375 rows=2542 loops=1)
Buffers: shared hit=95000
-> Nested Loop (cost=69.26..10683.57 rows=10 width=16) (actual time=5.101..1431.242 rows=30326 loops=1)
Buffers: shared hit=95000
-> Seq Scan on aw_benchmark bm (cost=0.00..1.81 rows=1 width=4) (actual time=0.034..0.040 rows=1 loops=1)
Filter: (rid = 11)
Buffers: shared hit=1
-> Nested Loop (cost=69.26..10681.66 rows=10 width=16) (actual time=5.056..1264.901 rows=30326 loops=1)
Buffers: shared hit=94999
-> Hash Join (cost=69.26..9481.38 rows=177 width=24) (actual time=4.951..981.338 rows=30326 loops=1)
Hash Cond: ((t.rid = bi.fk_benchmark_test) AND (ri.fk_benchmark_item = bi.rid))
Buffers: shared hit=3807
-> Hash Join (cost=2.21..9247.15 rows=16540 width=24) (actual time=0.722..920.115 rows=30326 loops=1)
Hash Cond: (ri.fk_benchmark_test = t.rid)
Buffers: shared hit=3793
-> Seq Scan on aw_benchmark_record_item ri (cost=0.00..7637.48 rows=384548 width=16) (actual time=0.061..407.944 rows=384601 loops=1)
Buffers: shared hit=3792
-> Hash (cost=2.16..2.16 rows=4 width=8) (actual time=0.035..0.035 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
Buffers: shared hit=1
-> Seq Scan on aw_benchmark_test t (cost=0.00..2.16 rows=4 width=8) (actual time=0.016..0.027 rows=4 loops=1)
Filter: (fk_benchmark = 11)
Buffers: shared hit=1
-> Hash (cost=35.22..35.22 rows=2122 width=8) (actual time=4.202..4.202 rows=2122 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 83kB
Buffers: shared hit=14
-> Seq Scan on aw_benchmark_item bi (cost=0.00..35.22 rows=2122 width=8) (actual time=0.059..2.050 rows=2122 loops=1)
Buffers: shared hit=14
-> Index Scan using aw_benchmark_record_pkey on aw_benchmark_record r (cost=0.00..6.77 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=30326)
Index Cond: ((fk_benchmark_test = t.rid) AND (fk_user = ri.fk_user))
Buffers: shared hit=91192
Total runtime: 1477.611 ms

++++ Row Counts ++++

aw_benchmark
66 rows

aw_benchmark_item
2122 rows

aw_benchmark_test
93 rows

aw_benchmark_record
60100 rows

aw_benchmark_record_item
383670 rows

++++ Table Definitions ++++

-- 66 rows --
CREATE TABLE "public"."aw_benchmark" (
"rid" int4 NOT NULL DEFAULT nextval('aw_benchmark_rid_seq'::regclass),
"name" varchar(100) NOT NULL,
"fk_owner" int4 NOT NULL,
"year" int4 NOT NULL,
CONSTRAINT "aw_benchmark_pkey" PRIMARY KEY ("rid") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_owner" FOREIGN KEY ("fk_owner") REFERENCES "public"."aw_user" ("rid") ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE
);

CREATE TABLE "public"."aw_benchmark_item" (
"rid" int4 NOT NULL DEFAULT nextval('aw_benchmark_item_rid_seq'::regclass),
"fk_benchmark_test" int4 NOT NULL,
"fk_test_item" int4 NOT NULL,
"ordering" int4 NOT NULL,
CONSTRAINT "aw_benchmark_item_pkey" PRIMARY KEY ("rid") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_benchmark_test" FOREIGN KEY ("fk_benchmark_test") REFERENCES "public"."aw_benchmark_test" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_test_item" FOREIGN KEY ("fk_test_item") REFERENCES "public"."aw_test_item" ("rid") ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE
);

-- 93 rows --
CREATE TABLE "public"."aw_benchmark_test" (
"rid" int4 NOT NULL DEFAULT nextval('aw_benchmark_test_rid_seq'::regclass),
"fk_benchmark" int4 NOT NULL,
"name" varchar(100) NOT NULL,
"ordering" int4 NOT NULL,
"is_assigned" bool NOT NULL DEFAULT false,
CONSTRAINT "aw_benchmark_test_pkey" PRIMARY KEY ("rid") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_benchmark" FOREIGN KEY ("fk_benchmark") REFERENCES "public"."aw_benchmark" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);

-- 60100 rows --
CREATE TABLE "public"."aw_benchmark_record" (
"fk_benchmark_test" int4 NOT NULL,
"fk_user" int4 NOT NULL,
"assigned" bool NOT NULL DEFAULT false,
"status" int4 NOT NULL DEFAULT 0,
"points_possible" int4 NOT NULL DEFAULT 0,
"points_received" int4 NOT NULL DEFAULT 0,
"randomize" bool NOT NULL DEFAULT true,
"time_started" timestamp(6) WITH TIME ZONE,
"time_completed" timestamp(6) WITH TIME ZONE,
"bubbled" bool NOT NULL DEFAULT false,
"reset_by" text,
"allow_review" bool NOT NULL DEFAULT false,
"reset_count" int2 NOT NULL DEFAULT 0,
"rescored" bool NOT NULL DEFAULT false,
"revised_points" bool DEFAULT false,
CONSTRAINT "aw_benchmark_record_pkey" PRIMARY KEY ("fk_benchmark_test", "fk_user") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_benchmark" FOREIGN KEY ("fk_benchmark_test") REFERENCES "public"."aw_benchmark_test" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_user" FOREIGN KEY ("fk_user") REFERENCES "public"."aw_user" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "fk_benchmark_test_fk_user_idx" ON "public"."aw_benchmark_record" USING btree(fk_benchmark_test ASC NULLS LAST, fk_user ASC NULLS LAST);
CREATE INDEX "fk_benchmark_test_idx" ON "public"."aw_benchmark_record" USING btree(fk_benchmark_test ASC NULLS LAST);
CREATE INDEX "fk_benchmark_test_status_idx" ON "public"."aw_benchmark_record" USING btree(fk_benchmark_test ASC NULLS LAST, status ASC NULLS LAST);

-- 383670 rows --
CREATE TABLE "public"."aw_benchmark_record_item" (
"fk_benchmark_test" int4 NOT NULL,
"fk_user" int4 NOT NULL,
"fk_benchmark_item" int4 NOT NULL,
"status" int4 NOT NULL DEFAULT 0,
"points_possible" int4 NOT NULL DEFAULT 1,
"points_received" int4 NOT NULL DEFAULT 0,
"seconds" int4,
"ordering" int2 NOT NULL DEFAULT 0,
"answer" varchar(4096),
"is_valid" bool NOT NULL DEFAULT true,
CONSTRAINT "aw_benchmark_record_item_pkey" PRIMARY KEY ("fk_benchmark_item", "fk_user") NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_benchmark" FOREIGN KEY ("fk_benchmark_test") REFERENCES "public"."aw_benchmark_test" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_user" FOREIGN KEY ("fk_user") REFERENCES "public"."aw_user" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "to_benchmark_item" FOREIGN KEY ("fk_benchmark_item") REFERENCES "public"."aw_benchmark_item" ("rid") ON UPDATE CASCADE ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "all_idx" ON "public"."aw_benchmark_record_item" USING btree(fk_benchmark_test ASC NULLS LAST, fk_user ASC NULLS LAST, fk_benchmark_item ASC NULLS LAST);