Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Прошу помощи с оптимизацией запроса / 4 сообщений из 4, страница 1 из 1
10.06.2008, 16:05
    #35367463
DDT
DDT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с оптимизацией запроса
Здравствуйте.

Есть вот такой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
SELECT
	jt.id AS jobticket_id,
	jt.number,
	jt.description,
	jt.delivering_date,
	jt.planstart_date,
	jt.planstop_date,
	jt.planlabour,
	jt.deviationcode,
	jt.status,
	jt.master_name AS master,
	jt.brigadier_name AS brigadier,
	
	td.id AS fixed_timedata_id,
	td.worker_id,
	td.date,
	td.worktime,
	td.standstill1,
	td.standstill2,
	td.standstill3,
	td.standstill4,

	p.name AS project,
	odn.number AS ordernumber,	
	c.code AS workcode,
	wt.name AS worktype,
	sd.name AS subdivision,
	sdg.name AS subcontractor
FROM
	surv_fixed_timedata td,
	surv_fixed_jobtickets jt,
	surv_fixed_projects p,
	surv_fixed_ordernumbers odn,
	surv_fixed_workcodes c,
	surv_fixed_worktypes wt,
	surv_fixed_subdivisions sd,
	surv_fixed_subdivisiongroups sdg
WHERE
	td.deleted <> TRUE AND
	td.documentname = 'jobtickets' AND
	td.document_id = jt.id AND
	td.timestart IS NOT NULL AND
	td.timestop IS NOT NULL AND
	
	jt.project_id = p.id AND
	jt.ordernumber_id = odn.id AND
	jt.workcode_id = c.id AND
	jt.worktype_id = wt.id AND
	td.subdivision_id = sd.id AND
	jt.subdivisiongroup_id = sdg.id
	AND jt.building_site_id IN ( 1 ,  2 )
	AND jt.project_id IN ( 1 ,  2 ,  4 ,  5 ,  8 ,  9 ,  10 ,  11 )
	AND td.date >= '2008-05-01'
	AND td.date <= '2008-05-31'
	AND jt.building_site_id = '2'
ORDER BY jt.id

Вот план выполнения:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
"Sort  (cost=15411.89..15414.57 rows=1074 width=359) (actual time=640.132..647.630 rows=3098 loops=1)"
"  Sort Key: jt.id"
"  ->  Hash Join  (cost=2041.35..15357.82 rows=1074 width=359) (actual time=222.189..615.582 rows=3098 loops=1)"
"        Hash Cond: ("outer".ordernumber_id = "inner".id)"
"        ->  Hash Join  (cost=2024.21..15324.57 rows=1074 width=351) (actual time=219.816..600.126 rows=3098 loops=1)"
"              Hash Cond: ("outer".project_id = "inner".id)"
"              ->  Hash Join  (cost=1976.00..15260.25 rows=1074 width=340) (actual time=216.097..584.546 rows=3098 loops=1)"
"                    Hash Cond: ("outer".workcode_id = "inner".id)"
"                    ->  Hash Join  (cost=1960.68..15228.81 rows=1074 width=331) (actual time=214.249..570.934 rows=3098 loops=1)"
"                          Hash Cond: ("outer".worktype_id = "inner".id)"
"                          ->  Hash Join  (cost=1953.36..15205.39 rows=1074 width=319) (actual time=213.243..551.013 rows=3098 loops=1)"
"                                Hash Cond: ("outer".subdivisiongroup_id = "inner".id)"
"                                ->  Hash Join  (cost=1935.94..15171.86 rows=1074 width=309) (actual time=212.078..537.702 rows=3098 loops=1)"
"                                      Hash Cond: ("outer".subdivision_id = "inner".id)"
"                                      ->  Hash Join  (cost=1909.66..15129.47 rows=1074 width=288) (actual time=210.026..522.569 rows=3098 loops=1)"
"                                            Hash Cond: ("outer".document_id = "inner".id)"
"                                            ->  Index Scan using "fixed_timedataIndex4" on surv_fixed_timedata td  (cost=0.00..13120.30 rows=11836 width=32) (actual time=82.468..327.895 rows=23618 loops=1)"
"                                                  Index Cond: ((date >= '2008-05-01'::date) AND (date <= '2008-05-31'::date))"
"                                            ->  Hash  (cost=1904.17..1904.17 rows=2197 width=260) (actual time=127.179..127.179 rows=1753 loops=1)"
"                                                  ->  Bitmap Heap Scan on surv_fixed_jobtickets jt  (cost=41.89..1904.17 rows=2197 width=260) (actual time=4.981..111.667 rows=1753 loops=1)"
"                                                        Recheck Cond: (((building_site_id = 2) AND (project_id = 1)) OR ((building_site_id = 2) AND (project_id = 2)) OR ((building_site_id = 2) AND (project_id = 4)) OR ((building_site_id = 2) AND (project_i (..)"
"                                                        ->  BitmapOr  (cost=41.89..41.89 rows=3148 width=0) (actual time=1.191..1.191 rows=0 loops=1)"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..5.31 rows=385 width=0) (actual time=0.225..0.225 rows=0 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 1))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..20.91 rows=2152 width=0) (actual time=0.059..0.059 rows=37 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 2))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.006..0.006 rows=0 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 4))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.127..0.127 rows=131 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 5))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.006..0.006 rows=0 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 8))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.004..0.004 rows=0 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 9))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.735..0.735 rows=1607 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 10))"
"                                                              ->  Bitmap Index Scan on "fixed_jobticketsIndex1"  (cost=0.00..2.61 rows=102 width=0) (actual time=0.007..0.007 rows=0 loops=1)"
"                                                                    Index Cond: ((building_site_id = 2) AND (project_id = 11))"
"                                      ->  Hash  (cost=26.02..26.02 rows=102 width=29) (actual time=1.980..1.980 rows=102 loops=1)"
"                                            ->  Seq Scan on surv_fixed_subdivisions sd  (cost=0.00..26.02 rows=102 width=29) (actual time=0.036..1.596 rows=102 loops=1)"
"                                ->  Hash  (cost=17.34..17.34 rows=34 width=18) (actual time=1.096..1.096 rows=34 loops=1)"
"                                      ->  Seq Scan on surv_fixed_subdivisiongroups sdg  (cost=0.00..17.34 rows=34 width=18) (actual time=0.053..0.945 rows=34 loops=1)"
"                          ->  Hash  (cost=7.25..7.25 rows=25 width=20) (actual time=0.936..0.936 rows=25 loops=1)"
"                                ->  Seq Scan on surv_fixed_worktypes wt  (cost=0.00..7.25 rows=25 width=20) (actual time=0.089..0.817 rows=25 loops=1)"
"                    ->  Hash  (cost=15.06..15.06 rows=106 width=17) (actual time=1.776..1.776 rows=106 loops=1)"
"                          ->  Seq Scan on surv_fixed_workcodes c  (cost=0.00..15.06 rows=106 width=17) (actual time=0.041..1.551 rows=106 loops=1)"
"              ->  Hash  (cost=48.17..48.17 rows=17 width=19) (actual time=3.653..3.653 rows=17 loops=1)"
"                    ->  Seq Scan on surv_fixed_projects p  (cost=0.00..48.17 rows=17 width=19) (actual time=0.149..3.520 rows=17 loops=1)"
"        ->  Hash  (cost=16.91..16.91 rows=91 width=16) (actual time=2.249..2.249 rows=91 loops=1)"
"              ->  Seq Scan on surv_fixed_ordernumbers odn  (cost=0.00..16.91 rows=91 width=16) (actual time=0.117..1.805 rows=91 loops=1)"
"Total runtime: 657.557 ms"

Наибольший вес тут имеет выполнение:
Код: plaintext
1.
2.
Index Scan using "fixed_timedataIndex4" on surv_fixed_timedata td  (cost=0.00..13120.30 rows=11836 width=32) (actual time=82.468..327.895 rows=23618 loops=1)"
Index Cond: ((date >= '2008-05-01'::date) AND (date <= '2008-05-31'::date))"

Вот индекс fixed_timedataIndex4:
Код: plaintext
1.
2.
3.
4.
5.
CREATE INDEX "fixed_timedataIndex4"
  ON surv_fixed_timedata
  USING btree
  (id, worker_id, date, worktime, standstill1, standstill2, standstill3, standstill4)
  WHERE deleted <> true AND timestart IS NOT NULL AND timestop IS NOT NULL AND documentname::text = 'jobtickets'::text;

Вопрос:
Можно ли сократить время выборки по данной таблице при прежних условиях?

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.06.2008, 17:21
    #35367748
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с оптимизацией запроса
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
...
WHERE
	td.deleted <> TRUE AND
	td.documentname = 'jobtickets' AND
	td.timestart IS NOT NULL AND
	td.timestop IS NOT NULL AND
	AND td.date >= '2008-05-01'
	AND td.date <= '2008-05-31' -- ограничения на td

	AND jt.project_id IN ( 1 ,  2 ,  4 ,  5 ,  8 ,  9 ,  10 ,  11 ) -- ограничения на jt
	AND jt.building_site_id = '2'

	td.document_id = jt.id AND -- связка td и jt

        ... -- остальные условия только на присоединение других таблиц

ORDER BY jt.id -- порядок результатов по jt.id
можно посмотреть, как изменится время выполнения запроса целиком и его частей, если объединение таблиц td и jt будет выполняться кроме HashJoin(jt,td) другими способами: HashJoin(td,jt), NestedLoop(jt,td), NestedLoop(td,jt), MergeJoin. особо попытаться добиться плана NestedLoop(jt,td), то есть сначала поиск по таблице jt, а затем для каждой найденной строки поиск по индексу td(document.id) соответствующих строк в td.

убрать ненужное условие jt.building_site_id IN (1, 2).

если условие jt.project_id IN (1, 2, 4, 5, 8, 9, 10, 11) - постоянное, то сделать частичный индекс с этим условием по jt(building_site_id,id). тогда при выполнении по плану NestedLoop(jt,td) даже отпадет необходимость в финальной сортировке.

Код: plaintext
1.
2.
3.
4.
Index Scan using "fixed_timedataIndex4"
Index Cond: ((date >= '2008-05-01'::date) AND (date <= '2008-05-31'::date))"

CREATE INDEX "fixed_timedataIndex4" ...
  (id, worker_id, date,..
может быть вы опечатались? поиск по индексу с условием date>= можно выполнить только в том случае, если date - первое поле в индексе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.06.2008, 17:44
    #35367807
DDT
DDT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с оптимизацией запроса
автор
будет выполняться кроме HashJoin(jt,td) другими способами: HashJoin(td,jt), NestedLoop(jt,td), NestedLoop(td,jt), MergeJoin


Каким образом можно изменить принцип объединения, использовать INNER (OUTER) JOIN ?

автор
если условие jt.project_id IN (1, 2, 4, 5, 8, 9, 10, 11) - постоянное, то сделать частичный индекс с этим условием ...


Данное условие не постоянное, оно формируется приложением на основе переменных окружения.

автор
может быть вы опечатались? поиск по индексу с условием date>= можно выполнить только в том случае, если date - первое поле в индексе.


Нет, не опечатался. Вот запрос на создание индекса:
Код: plaintext
1.
2.
3.
4.
5.
CREATE INDEX "fixed_timedataIndex4"
  ON surv_fixed_timedata
  USING btree
  (id, worker_id, date, worktime, standstill1, standstill2, standstill3, standstill4)
  WHERE deleted <> true AND timestart IS NOT NULL AND timestop IS NOT NULL AND documentname::text = 'jobtickets'::text;

Вероятно, что я неправильно его составил. Если поле data стоит в индексе не первым, то оно не участвует в поиске?

P.S.
Версия Postgres 8.2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2008, 01:48
    #35368379
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прошу помощи с оптимизацией запроса
DDTКаким образом можно изменить принцип объединения, использовать INNER (OUTER) JOIN ?нет. с помощью изменения параметров планировщика set enable_* to off|on, например для начала сделать set enable_hashjoin to off. ещё создать нужные для определенного плана индексы, и даже возможно удалить ненужные, например, как я уже писал, для плана NestedLoop(jt,td) создать индекс по td(document.id).

DDTНет, не опечатался. Вот запрос на создание индекса:
Код: plaintext
CREATE INDEX "fixed_timedataIndex4" ... (id, worker_id, date,..

Вероятно, что я неправильно его составил. Если поле data стоит в индексе не первым, то оно не участвует в поиске?да, эффективный поиск (в плане обозначается IndexCond) по такому индексу с ограничением по полю date (на равенство или на неравенство) возможен лишь в том случае, если кроме этого присутствуют ограничения по полю id на равенство и по полю worker_id на равенство.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Прошу помощи с оптимизацией запроса / 4 сообщений из 4, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]