|
제가 가지고 있는 두가지 쿼리는 조건문의 값만 틀리고 다른 곳은 똑같습니다.
그런데 1번쿼리는 속도가 빠른데 2번 쿼리는 속도가 느리네요. 10분도 더 걸립니다.
조언을 좀 부탁드려요.
sampleTime 컬럼의 인덱스를 다른걸로 잡아줘야 할까요?
----------1번 쿼리: incarnaion 값이 500개 정도 , 실행속도: 1초미만 , 결과값 700행 정도
SELECT site_id,
"Incarnation",
EXTRACT(EPOCH FROM "SampleTime") * 1000 as "SampleTime",
"Sample_id",
"StatisticID",
"Value"
FROM "usdcLogDetails_1" AS uld
JOIN "usdcLogStatistics" AS uls
ON uld.id=uls."Sample_id" WHERE (site_id=1 AND (("Incarnation"=18 AND "StatisticID"=140)))
AND "SampleTime"
BETWEEN TO_TIMESTAMP(1266307200000 / 1000) AND TO_TIMESTAMP(1266393600000 / 1000)
ORDER BY "SampleTime" ASC
CREATE TABLE "usdcLogDetails_1"
(
id bigserial NOT NULL,
site_id integer NOT NULL DEFAULT 0,
"Log_id" integer NOT NULL,
"SampleTime" timestamp with time zone NOT NULL,
"Incarnation" integer NOT NULL,
CONSTRAINT "usdcLogDetails_1_pkey" PRIMARY KEY (id),
CONSTRAINT "usdcLogDetails_1_Log_id_fkey" FOREIGN KEY ("Log_id")
REFERENCES "usdcLogMaster" (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT "usdcLogDetails_1_Log_id_fkey1" FOREIGN KEY ("Log_id", "Incarnation")
REFERENCES "usdcLogSourceDescription_1" ("Log_id", "Incarnation") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE "usdcLogDetails_1" OWNER TO mhwang;
CREATE INDEX "usdcLogDetails_1_index_Incarnation"
ON "usdcLogDetails_1"
USING btree
("Incarnation");
CREATE INDEX "usdcLogDetails_1_index_SampleTime"
ON "usdcLogDetails_1"
USING btree
("SampleTime");
explain analyze 결과
"Sort (cost=490.21..490.21 rows=1 width=36) (actual time=1471.000..1471.000 rows=732 loops=1)"
" Sort Key: (date_part('epoch'::text, uld."SampleTime") * 1000::double precision)"
" -> Nested Loop (cost=135.08..490.20 rows=1 width=36) (actual time=188.000..1471.000 rows=732 loops=1)"
" Join Filter: (uld.id = uls."Sample_id")"
" -> Bitmap Heap Scan on "usdcLogDetails_1" uld (cost=129.35..191.05 rows=1 width=24) (actual time=188.000..190.000 rows=746 loops=1)"
" Recheck Cond: (("Incarnation" = 18) AND ("SampleTime" >= '2010-02-16 00:00:00-08'::timestamp with time zone) AND ("SampleTime" <= '2010-02-17 00:00:00-08'::timestamp with time zone))"
" Filter: (site_id = 1)"
" -> BitmapAnd (cost=129.35..129.35 rows=16 width=0) (actual time=188.000..188.000 rows=0 loops=1)"
" -> Bitmap Index Scan on "usdcLogDetails_1_index_Incarnation" (cost=0.00..60.52 rows=3226 width=0) (actual time=0.000..0.000 rows=746 loops=1)"
" Index Cond: ("Incarnation" = 18)"
" -> Bitmap Index Scan on "usdcLogDetails_1_index_SampleTime" (cost=0.00..68.58 rows=3226 width=0) (actual time=188.000..188.000 rows=645156 loops=1)"
" Index Cond: (("SampleTime" >= '2010-02-16 00:00:00-08'::timestamp with time zone) AND ("SampleTime" <= '2010-02-17 00:00:00-08'::timestamp with time zone))"
" -> Bitmap Heap Scan on "usdcLogStatistics" uls (cost=5.73..296.76 rows=191 width=20) (actual time=0.332..1.087 rows=1915 loops=746)"
" Recheck Cond: ("StatisticID" = 140)"
" -> Bitmap Index Scan on "usdcLogStatistics_index_StatisticID" (cost=0.00..5.69 rows=191 width=0) (actual time=0.276..0.276 rows=1915 loops=746)"
" Index Cond: ("StatisticID" = 140)"
"Total runtime: 1471.000 ms"
----------------2번 쿼리: incarnaion 값이 10만개 정도, 실행속도: 너무 길어 기억이 안날 정도임, between 절을 빼고 쿼리를 날리면 속도가 엄청 빠름 , 결과값 200행정도
SELECT site_id,
"Incarnation",
--EXTRACT(EPOCH FROM "SampleTime") * 1000 as "SampleTime",
"SampleTime",
"Sample_id",
"StatisticID",
"Value"
FROM "usdcLogDetails_1" AS uld
JOIN "usdcLogStatistics" AS uls
ON uld.id=uls."Sample_id" WHERE (site_id=1 AND (("Incarnation"=2 AND "StatisticID"=140)))
AND "SampleTime"
BETWEEN '2010-02-16 15:05:30.393-08' AND '2010-02-16 16:49:40.384-08'
ORDER BY "SampleTime" ASC
CREATE TABLE "usdcLogStatistics"
(
id bigserial NOT NULL,
"Sample_id" bigint NOT NULL,
"StatisticID" integer,
"Value" bigint NOT NULL,
CONSTRAINT "usdcLogStatistics_pkey" PRIMARY KEY (id),
CONSTRAINT "usdcLogStatistics_Sample_id_fkey" FOREIGN KEY ("Sample_id")
REFERENCES "usdcLogDetails_1" (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (OIDS=FALSE);
ALTER TABLE "usdcLogStatistics" OWNER TO mhwang;
CREATE INDEX "usdcLogStatistics_index_StatisticID"
ON "usdcLogStatistics"
USING btree
("StatisticID");
explain analyze 결과
"Sort (cost=490.20..490.21 rows=1 width=36) (actual time=848705.000..848705.000 rows=210 loops=1)"
" Sort Key: uld."SampleTime""
" -> Nested Loop (cost=135.08..490.19 rows=1 width=36) (actual time=336.000..848705.000 rows=210 loops=1)"
" Join Filter: (uld.id = uls."Sample_id")"
" -> Bitmap Heap Scan on "usdcLogDetails_1" uld (cost=129.35..191.05 rows=1 width=24) (actual time=336.000..843.000 rows=501246 loops=1)"
" Recheck Cond: (("Incarnation" = 2) AND ("SampleTime" >= '2010-02-16 15:05:30.393-08'::timestamp with time zone) AND ("SampleTime" <= '2010-02-16 16:49:40.384-08'::timestamp with time zone))"
" Filter: (site_id = 1)"
" -> BitmapAnd (cost=129.35..129.35 rows=16 width=0) (actual time=334.000..334.000 rows=0 loops=1)"
" -> Bitmap Index Scan on "usdcLogDetails_1_index_Incarnation" (cost=0.00..60.52 rows=3226 width=0) (actual time=180.000..180.000 rows=501246 loops=1)"
" Index Cond: ("Incarnation" = 2)"
" -> Bitmap Index Scan on "usdcLogDetails_1_index_SampleTime" (cost=0.00..68.58 rows=3226 width=0) (actual time=154.000..154.000 rows=645155 loops=1)"
" Index Cond: (("SampleTime" >= '2010-02-16 15:05:30.393-08'::timestamp with time zone) AND ("SampleTime" <= '2010-02-16 16:49:40.384-08'::timestamp with time zone))"
" -> Bitmap Heap Scan on "usdcLogStatistics" uls (cost=5.73..296.76 rows=191 width=20) (actual time=0.339..1.057 rows=1915 loops=501246)"
" Recheck Cond: ("StatisticID" = 140)"
" -> Bitmap Index Scan on "usdcLogStatistics_index_StatisticID" (cost=0.00..5.69 rows=191 width=0) (actual time=0.300..0.300 rows=1915 loops=501246)"
" Index Cond: ("StatisticID" = 140)"
|
