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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
