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); -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
