Slow query?

Поиск

Список

Период

Сортировка

От	Waruna Geekiyanage
Тема	Slow query?
Дата	9 сентября 2003 г. 06:28:41
Msg-id	003201c3769b$75e7f490$81545ecb@nirmani обсуждение исходный текст
Список	pgsql-performance

Дерево обсуждения

Hi All,

Is it usual that the following query to take 22 secs with the machine I have?

Any other reason?

Hope I have provided all the details need.

Thanks,

Waruna

Tables:

/* --------------------------------------------------------
Table structure for table "tvDiary"
-------------------------------------------------------- */
CREATE TABLE "tvDiary" (
    "member" int4 NOT NULL,
    "timeSlot" int2 NOT NULL references "timeSlot"("code"),
   "channel" varchar(4) NOT NULL references "tvChannel"("code"),
   "date" date NOT NULL,
   CONSTRAINT "tvDiary_pkey" PRIMARY KEY ("date", "member", "timeSlot")
);
Indexed on "date"

/* --------------------------------------------------------
Table structure for table "mDiary"
-------------------------------------------------------- */
CREATE TABLE "mDiary" (
   "member" int4 NOT NULL,
   "area" char(1) NOT NULL,
   "district" int2 references "district"("code"),
   "date" date NOT NULL,
   CONSTRAINT "mDiary_pkey" PRIMARY KEY ("date", "member")
);
Indexed on "date"

# Records
tvDiary : 7 300 000
mDiary : 850 000

machine :
Celeron 1.0GHz RAM - 390MB , 40 GB IDE HDD
RedHat Linux 9

kernel.shmmni = 4096
kernel.shmall = 33554432
kernel.shmmax = 134217728

postgres 7.3.4

shared_buffers = 8192
sort_mem = 65536

Query:

SELECT COUNT(td.member) AS count, td.date AS date, td."timeSlot" AS "timeSlot", td.channel AS channel,

    tg.district AS district,tg.area AS area
FROM "tvDiary" td ,(SELECT DISTINCT(md.member) AS member, md.area AS area, md.district as district
                            FROM "mDiary" md
                             WHERE (md.date BETWEEN '20020301' AND '20020330') ) AS tg
WHERE(td.date BETWEEN '20020301' AND '20020330') AND (td.member=tg.member)
GROUP BY td.date,td."timeSlot", td.channel,tg.district,tg.area;

QUERY PLAN

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------
Aggregate (cost=91790.44..100942.65 rows=52298 width=28) (actual time=18396.42..21764.44 rows=57478 loops=1)
   -> Group (cost=91790.44..99635.19 rows=522983 width=28) (actual time=18396.34..21158.23 rows=281733 loops=1)
         -> Sort (cost=91790.44..93097.90 rows=522983 width=28) (actual time=18396.30..18588.91 rows=281733 loops=1)
               Sort Key: td.date, td."timeSlot", td.channel, tg.district, tg.area
               -> Merge Join (cost=34290.10..42116.42 rows=522983 width=28) (actual time=8159.30..10513.62 rows=281733 ops=1)
                     Merge Cond: ("outer".member = "inner".member)
                     -> Sort (cost=29121.48..29755.35 rows=253551 width=17) (actual time=6752.36..6933.38 rows=282552 loops=1)
                           Sort Key: td.member
                           -> Index Scan using d_tvdiary_key on "tvDiary" td (cost=0.00..6362.82 rows=253551 width=17) (actual time=95.80..4766.25 rows=282587
loops=1)
                                 Index Cond: ((date >= '2002-03-01'::date) AND (date <= '2002-03-30'::date))
                     -> Sort (cost=5168.63..5179.26 rows=4251 width=11) (actual time=1406.88..1590.72 rows=281955 loops=1)
                           Sort Key: tg.member
                           -> Subquery Scan tg (cost=4487.31..4912.42 rows=4251 width=11) (actual time=1228.55..1397.20 rows=2348 loops=1)
                                 -> Unique (cost=4487.31..4912.42 rows=4251 width=11) (actual time=1228.52..1390.12 rows=2348 loops=1)
                                       -> Sort (cost=4487.31..4593.59 rows=42511 width=11) (actual time=1228.51..1257.87 rows=46206 loops=1)
                                             Sort Key: member, area, district
                                             -> Index Scan using d_mdiary_key on "mDiary" md (cost=0.00..1219.17 rows=42511 width=11) (actual time=60.20..750.
67 rows=46206 loops=1)
                                                   Index Cond: ((date >= '2002-03-01'::date) AND (date <= '2002-03-30'::date))
Total runtime: 21992.24 msec
(19 rows)

В списке pgsql-performance по дате отправления:

Предыдущее

От: Tom Lane
Дата: 09 сентября 2003 г., 03:15:15
Сообщение: Re: Quick question

Следующее

От: JM
Дата: 09 сентября 2003 г., 07:53:29
Сообщение: increase performancr with "noatime"?

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Slow query?

Предыдущее

Следующее