Оптимизация запроса / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса

25 сообщений из 33, страница 1 из 2

все

Оптимизация запроса

#39712914

rinace

Гость

Поставлена задача кардинально улучшить производительность запроса вида :

SELECT pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
s."REPORT_NAME" AS report_name,
p."SOITECPARAM_NAME" AS soitecparam_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wafer_data w,
shipment s,
parameters p,
shipment_pc sp,
pc pc
WHERE s."SHIPMENT_ID" = w."SHIPMENT_ID" AND p."PARAMETER_ID" = w."PARAMETER_ID" AND s."SHIPMENT_ID" = sp."SHIPMENT_ID" AND pc."PC_ID" = sp."PC_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.

explain analyze select * from vw_dataextractor_waferdata where pc_name in ('PC_NAME_64' , 'PC_NAME_39' , 'PC_NAME_62' , 'PC_NAME_25' , 'PC_NAME_93')  ;

                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=57.23..543.13 rows=8000 width=545) (actual time=2.102..42.238 rows=9960 loops=1)
   Hash Cond: (w."PARAMETER_ID" = p."PARAMETER_ID")
   ->  Nested Loop  (cost=55.33..431.23 rows=8000 width=495) (actual time=2.038..34.831 rows=9960 loops=1)
         ->  Hash Join  (cost=3.21..4.64 rows=2 width=78) (actual time=0.086..0.120 rows=3 loops=1)
               Hash Cond: (s."SHIPMENT_ID" = sp."SHIPMENT_ID")
               ->  Seq Scan on shipment s  (cost=0.00..1.30 rows=30 width=42) (actual time=0.004..0.018 rows=30 loops=1)
               ->  Hash  (cost=3.18..3.18 rows=2 width=36) (actual time=0.073..0.073 rows=3 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Hash Join  (cost=1.89..3.18 rows=2 width=36) (actual time=0.062..0.071 rows=3 loops=1)
                           Hash Cond: (sp."PC_ID" = pc."PC_ID")
                           ->  Seq Scan on shipment_pc sp  (cost=0.00..1.20 rows=20 width=8) (actual time=0.004..0.007 rows=20 loops=1)
                           ->  Hash  (cost=1.81..1.81 rows=6 width=36) (actual time=0.050..0.050 rows=6 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 ->  Seq Scan on pc  (cost=0.00..1.81 rows=6 width=36) (actual time=0.023..0.046 rows=6 loops=1)
                                       Filter: (("PC_NAME")::text = ANY ('{PC_NAME_64,PC_NAME_39,PC_NAME_62,PC_NAME_25,PC_NAME_93}'::text[]))
                                       Rows Removed by Filter: 44
         ->  Bitmap Heap Scan on wafer_data w  (cost=52.12..179.96 rows=3333 width=429) (actual time=1.158..5.340 rows=3320 loops=3)
               Recheck Cond: ("SHIPMENT_ID" = s."SHIPMENT_ID")
               Heap Blocks: exact=2188
               ->  Bitmap Index Scan on waferdata_idx3  (cost=0.00..51.29 rows=3333 width=0) (actual time=0.996..0.996 rows=3320 loops=3)
                     Index Cond: ("SHIPMENT_ID" = s."SHIPMENT_ID")
   ->  Hash  (cost=1.40..1.40 rows=40 width=58) (actual time=0.050..0.050 rows=40 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 12kB
         ->  Seq Scan on parameters p  (cost=0.00..1.40 rows=40 width=58) (actual time=0.012..0.028 rows=40 loops=1)
 Planning time: 2.813 ms
 Execution time: 43.283 ms

Представление :
CREATE OR REPLACE VIEW public.vw_dataextractor_waferdata AS
SELECT pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
s."REPORT_NAME" AS report_name,
p."SOITECPARAM_NAME" AS soitecparam_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wafer_data w,
shipment s,
parameters p,
shipment_pc sp,
pc pc
WHERE s."SHIPMENT_ID" = w."SHIPMENT_ID" AND p."PARAMETER_ID" = w."PARAMETER_ID" AND s."SHIPMENT_ID" = sp."SHIPMENT_ID" AND pc."PC_ID" = sp."PC_ID";

CREATE OR REPLACE RULE "_RETURN" AS
ON SELECT TO public.vw_dataextractor_waferdata
DO INSTEAD
SELECT pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
s."REPORT_NAME" AS report_name,
p."SOITECPARAM_NAME" AS soitecparam_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wafer_data w,
shipment s,
parameters p,
shipment_pc sp,
pc pc
WHERE s."SHIPMENT_ID" = w."SHIPMENT_ID" AND p."PARAMETER_ID" = w."PARAMETER_ID" AND s."SHIPMENT_ID" = sp."SHIPMENT_ID" AND pc."PC_ID" = sp."PC_ID";

Таблица wafer_data порядка 2-х миллионов строк

Table "public.wafer_data"
Column | Type | Modifiers
-----------------------+-----------------------+-----------
RUN_ID | integer |
LASERMARK | character varying(20) | not null
LOTID | character varying(80) |
SHIPMENT_ID | integer | not null
PARAMETER_ID | integer | not null
SOITEC_INTERNAL_VALUE | character varying(75) |
REPORTED_VALUE | character varying(75) |
LOWER_SPEC_LIMIT | numeric |
UPPER_SPEC_LIMIT | numeric |
Indexes:
"waferdata_idx1" btree ("SHIPMENT_ID", "PARAMETER_ID")
"waferdata_idx2" btree ("LASERMARK")
"waferdata_idx3" btree ("SHIPMENT_ID")

Таблица shipment порядка 2-х тысяч строк

Table "public.shipment"
Column | Type | Modifiers
---------------+-----------------------------+----------------------------------------------------------------
SHIPMENT_ID | integer | not null default nextval('shipment_shipment_id_seq'::regclass)
SHIPMENT_NAME | character varying(30) |
SHIPMENT_DATE | timestamp without time zone |
REPORT_NAME | character varying(40) |
Indexes:
"shipment_pkey" PRIMARY KEY, btree ("SHIPMENT_ID")
"unique_shipment_name_date_report" UNIQUE CONSTRAINT, btree ("SHIPMENT_NAME", "SHIPMENT_DATE", "REPORT_NAME")
"shipment_idx1" btree ("SHIPMENT_ID", "SHIPMENT_DATE")
"shipment_idx2" btree ("SHIPMENT_DATE")
"shipment_idx3" btree ("SHIPMENT_ID")

Таблица parameters порядка 22 000 строк
Table "public.parameters"
Column | Type | Modifiers
--------------------+-----------------------+---------------------------------------------------------
PARAMETER_ID | integer | not null default nextval('parameters_id_seq'::regclass)
PC_ID | integer |
SOITECPARAM_NAME | character varying(50) |
CUSTOMERPARAM_NAME | character varying(50) |
CUSTOMERPARAM_UNIT | character varying(32) |
TYPE_CALCUL | character varying(50) |
Indexes:
"unique_parameter" UNIQUE CONSTRAINT, btree ("PC_ID", "SOITECPARAM_NAME", "CUSTOMERPARAM_NAME", "CUSTOMERPARAM_UNIT", "TYPE_CALCUL")
"parameters_idx1" btree ("PARAMETER_ID", "CUSTOMERPARAM_NAME")

Таблица shipment_pc порядка 2 000 строе

Table "public.shipment_pc"
Column | Type | Modifiers
-------------+---------+----------------------------------------------------------
ID | integer | not null default nextval('shipment_pc_id_seq'::regclass)
SHIPMENT_ID | integer |
PC_ID | integer |
Indexes:
"unique_shipment_pc" UNIQUE CONSTRAINT, btree ("SHIPMENT_ID", "PC_ID")
"shipmentpc_idx1" btree ("SHIPMENT_ID", "PC_ID")

Таблица pc порядка 100 строк

Table "public.pc"
Column | Type | Modifiers
---------------------+-----------------------+----------------------------------------------------
PC_ID | integer | not null default nextval('pc_pc_id_seq'::regclass)
PC_NAME | character varying(50) |
CUSTOMER_PARTNUMBER | character varying(30) |
CUSTOMERFACTORY_ID | character varying(30) |
Indexes:
"unique_pc" UNIQUE CONSTRAINT, btree ("PC_NAME", "CUSTOMER_PARTNUMBER", "CUSTOMERFACTORY_ID")
"pc_idx1" btree ("PC_NAME")

Просьба сразу, не тратить время на предложения типа «пересчитать статистику».

Мое личное мнение – задача не решаемая , кардинально улучшить ситуацию нельзя , потому, что самая большая таблица служит для реализации отношения многие-ко-многим .

Секционирование таблицы wafer_data по ключам SHIPMENT_ID или PARAMETER_ID – увеличивает стоимость запроса .

Но вдруг у кого найдется свежее решение.

...

Рейтинг:

0 / 0

04.10.2018, 12:11

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712916

jan2ary

Участник

Откуда: Киев

Сообщения: 1 766

Рейтинг: 0 / 0

rinace,

Execution time: 43.283 ms
А какое желаемое время по сравнению с 43 мс?

...

Рейтинг:

0 / 0

04.10.2018, 12:17

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712935

rinace

Гость

jan2aryrinace,

Execution time: 43.283 ms
А какое желаемое время по сравнению с 43 мс?
Это время с тестовой маленькой базы, на продакшн время выполнения чуть побольше.
Вопрос конечно больше теоретический, запрос получен в результате исследований и попыток улучшить время выполнения другого запроса .
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from "SHIPMENT_DATE") AS year,
extract(month from "SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."SOITECPARAM_NAME" AS soitecparam_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM wafer_data w,
shipment s,
parameters p,
shipment_pc sp,
pc pc
WHERE s."SHIPMENT_ID" = w."SHIPMENT_ID"
AND p."PARAMETER_ID" = w."PARAMETER_ID"
AND s."SHIPMENT_ID" = sp."SHIPMENT_ID"
AND pc."PC_ID" = sp."PC_ID"
AND s."SHIPMENT_DATE" >= '2018-04-03' AND s."SHIPMENT_DATE" <= '2018-08-03'
and s."SHIPMENT_DATE" = (SELECT MAX(s2."SHIPMENT_DATE")
FROM shipment s2,
wafer_data w2
WHERE s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
AND w2."LASERMARK" = w."LASERMARK");

Со следующим планом

Код: 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.

QUERY PLAN                                                                                  
   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---
 Nested Loop  (cost=2.06..2796993.10 rows=133 width=565) (actual time=132975.219..132975.219 rows=0 loops=1)
   Join Filter: (w."PARAMETER_ID" = p."PARAMETER_ID")
   ->  Hash Join  (cost=2.06..2796911.14 rows=133 width=495) (actual time=132975.217..132975.217 rows=0 loops=1)
         Hash Cond: ((w."SHIPMENT_ID" = s."SHIPMENT_ID") AND ((SubPlan 1) = s."SHIPMENT_DATE"))
         ->  Merge Join  (cost=0.43..6458.44 rows=67277 width=465) (actual time=0.110..491.354 rows=66948 loops=1)
               Merge Cond: (w."SHIPMENT_ID" = sp."SHIPMENT_ID")
               ->  Index Scan using waferdata_idx1 on wafer_data w  (cost=0.29..5547.01 rows=100000 width=429) (actual time=0.064..241.422 rows=93410 loops=1)
               ->  Materialize  (cost=0.14..29.11 rows=20 width=36) (actual time=0.040..56.933 rows=63642 loops=1)
                     ->  Nested Loop  (cost=0.14..29.06 rows=20 width=36) (actual time=0.034..0.479 rows=20 loops=1)
                           Join Filter: (sp."PC_ID" = pc."PC_ID")
                           Rows Removed by Join Filter: 980
                           ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.14..12.44 rows=20 width=8) (actual time=0.008..0.096 rows=20 loops=1)
                                 Heap Fetches: 20
                           ->  Materialize  (cost=0.00..1.75 rows=50 width=36) (actual time=0.001..0.007 rows=50 loops=20)
                                 ->  Seq Scan on pc  (cost=0.00..1.50 rows=50 width=36) (actual time=0.009..0.021 rows=50 loops=1)
         ->  Hash  (cost=1.45..1.45 rows=12 width=42) (actual time=0.019..0.019 rows=12 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Seq Scan on shipment s  (cost=0.00..1.45 rows=12 width=42) (actual time=0.006..0.012 rows=12 loops=1)
                     Filter: (("SHIPMENT_DATE" >= '2018-04-03 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-08-03 00:00:00'::timestamp without time zone
))
                     Rows Removed by Filter: 18
         SubPlan 1
           ->  Aggregate  (cost=828.01..828.02 rows=1 width=8) (actual time=1.972..1.972 rows=1 loops=66948)
                 ->  Hash Join  (cost=25.77..825.53 rows=990 width=8) (actual time=0.363..1.816 rows=996 loops=66948)
                       Hash Cond: (w2."SHIPMENT_ID" = s2."SHIPMENT_ID")
                       ->  Bitmap Heap Scan on wafer_data w2  (cost=24.09..811.65 rows=990 width=4) (actual time=0.353..1.361 rows=996 loops=66948)
                             Recheck Cond: (("LASERMARK")::text = (w."LASERMARK")::text)
                             Heap Blocks: exact=36569523
                             ->  Bitmap Index Scan on waferdata_idx2  (cost=0.00..23.84 rows=990 width=0) (actual time=0.251..0.251 rows=996 loops=66948)
                                   Index Cond: (("LASERMARK")::text = (w."LASERMARK")::text)
                       ->  Hash  (cost=1.30..1.30 rows=30 width=12) (actual time=0.016..0.016 rows=30 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 10kB
                             ->  Seq Scan on shipment s2  (cost=0.00..1.30 rows=30 width=12) (actual time=0.003..0.009 rows=30 loops=1)
   ->  Materialize  (cost=0.00..1.60 rows=40 width=58) (never executed)
         ->  Seq Scan on parameters p  (cost=0.00..1.40 rows=40 width=58) (never executed)
 Planning time: 3.998 ms
 Execution time: 132975.895 ms
(36 rows)

Это с тестовой базы, на продакшн запрос выполняется пару часов, почему собственно и возникла задача.
В ходе попыток улучшить запрос, возникла идея о секционировании, однако оказалось , секционирование в данном случае ничем не помогает.
Этот запрос несколько похож на исходный, поэтому чтобы не усложнять и не перегружать и был задан вопрос по возможности оптимизировать конструкцию , хотя запрос сильно другой, согласен.

Возможно запрос из первого сообщения нельзя чем то улучшить в отличии от текущего.

...

Рейтинг:

0 / 0

04.10.2018, 12:38

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712956

Ы2

Участник

Сообщения: 233

Рейтинг: 0 / 0

rinace, у вас два последовательных просмотра shipments: один из-за отсутствия индекса по shipment_date, второй из-за max().

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

s."SHIPMENT_DATE" >= '2018-04-03' AND s."SHIPMENT_DATE" <= '2018-08-03'
	and s."SHIPMENT_DATE" = (
		SELECT
			MAX(s2."SHIPMENT_DATE")
		FROM
			shipment s2,
			wafer_data w2
		WHERE
			s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
			AND w2."LASERMARK" = w."LASERMARK"
		)

Здесь, как я понимаю, max() имеет смысл только, если попадает между указанными датами.

...

Рейтинг:

0 / 0

04.10.2018, 13:05

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712972

rinace

Гость

Ы2rinace, у вас два последовательных просмотра shipments: один из-за отсутствия индекса по shipment_date, второй из-за max().

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

s."SHIPMENT_DATE" >= '2018-04-03' AND s."SHIPMENT_DATE" <= '2018-08-03'
	and s."SHIPMENT_DATE" = (
		SELECT
			MAX(s2."SHIPMENT_DATE")
		FROM
			shipment s2,
			wafer_data w2
		WHERE
			s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
			AND w2."LASERMARK" = w."LASERMARK"
		)

Здесь, как я понимаю, max() имеет смысл только, если попадает между указанными датами.

А вот это уж что-то.
Спасибо

...

Рейтинг:

0 / 0

04.10.2018, 13:31

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712979

rinace

Гость

Ы2rinace, у вас два последовательных просмотра shipments: один из-за отсутствия индекса по shipment_date, второй из-за max().

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

s."SHIPMENT_DATE" >= '2018-04-03' AND s."SHIPMENT_DATE" <= '2018-08-03'
	and s."SHIPMENT_DATE" = (
		SELECT
			MAX(s2."SHIPMENT_DATE")
		FROM
			shipment s2,
			wafer_data w2
		WHERE
			s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
			AND w2."LASERMARK" = w."LASERMARK"
		)

Здесь, как я понимаю, max() имеет смысл только, если попадает между указанными датами.

да, смысл max именно такой.

Однако почему возникает Seq Scan пока не понятно
Индексы имеются
shipment
Table "public.shipment"
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+-----------------------------------------------
SHIPMENT_ID | integer | | not null | nextval('shipment_shipment_id_seq'::regclass)
SHIPMENT_NAME | character varying(30) | | |
SHIPMENT_DATE | timestamp without time zone | | |
REPORT_NAME | character varying(40) | | |
Indexes:
"shipment_pkey" PRIMARY KEY, btree ("SHIPMENT_ID")
"unique_shipment_name_date_report" UNIQUE CONSTRAINT, btree ("SHIPMENT_NAME", "SHIPMENT_DATE", "REPORT_NAME")
"shipment_idx1" btree ("SHIPMENT_ID", "SHIPMENT_DATE")
"shipment_idx2" btree ("SHIPMENT_DATE")
"shipment_idx3" btree ("SHIPMENT_ID")

...

Рейтинг:

0 / 0

04.10.2018, 13:36

| Ответить | Цитировать | Написать

Оптимизация запроса

#39712988

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

rinace,

Код: sql

1.
2.
3.

 Filter: (("SHIPMENT_DATE" >= '2018-04-03 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-08-03 00:00:00'::timestamp without time zone
))
                     Rows Removed by Filter: 18

если в бою то же -- бороться не за что

...

Рейтинг:

0 / 0

04.10.2018, 13:48

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713006

rinace

Гость

qwwq,

В боевой чуть-чуть по другому оказывается , индекс по SHIPMENT_DATA использован, но там и строк в таблице сильно побольше

Код: 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.

                                                                                            QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=935.84..479763226.18 rows=3322 width=135) (actual time=31.536..8220420.295 rows=8111656 loops=1)
   Join Filter: (sp."PC_ID" = pc."PC_ID")
   Rows Removed by Join Filter: 1273529992
   ->  Nested Loop  (cost=935.84..479753606.45 rows=3322 width=100) (actual time=30.936..7645168.609 rows=8111656 loops=1)
         Join Filter: (s."SHIPMENT_ID" = sp."SHIPMENT_ID")
         ->  Nested Loop  (cost=935.56..479750087.85 rows=3317 width=104) (actual time=30.891..7615692.211 rows=8111656 loops=1)
               ->  Hash Join  (cost=935.15..479742547.58 rows=3317 width=76) (actual time=30.875..7577305.146 rows=8111656 loops=1)
                     Hash Cond: ((w."SHIPMENT_ID" = s."SHIPMENT_ID") AND ((SubPlan 1) = s."SHIPMENT_DATE"))
                     ->  Seq Scan on wafer_data w  (cost=0.00..572942.21 rows=26829621 width=46) (actual time=0.007..11507.087 rows=26822469 loops=1)
                     ->  Hash  (cost=923.54..923.54 rows=774 width=30) (actual time=0.942..0.942 rows=779 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 58kB
                           ->  Index Scan using shipment_idx2 on shipment s  (cost=0.28..923.54 rows=774 width=30) (actual time=0.019..0.644 rows=779 loops=1)
                                 Index Cond: (("SHIPMENT_DATE" >= '2018-07-01 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-09-30 00:00:00'::timestamp without time zone))
                     SubPlan 1
                       ->  Aggregate  (cost=356.88..356.89 rows=1 width=8) (actual time=0.215..0.215 rows=1 loops=34934125)
                             ->  Nested Loop  (cost=0.84..356.74 rows=56 width=8) (actual time=0.020..0.196 rows=54 loops=34934125)
                                   ->  Index Scan using waferdata_idx2 on wafer_data w2  (cost=0.56..228.08 rows=56 width=4) (actual time=0.017..0.059 rows=54 loops=34934125)
                                         Index Cond: (("LASERMARK")::text = (w."LASERMARK")::text)
                                   ->  Index Only Scan using shipment_idx1 on shipment s2  (cost=0.28..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1880840957)
                                         Index Cond: ("SHIPMENT_ID" = w2."SHIPMENT_ID")
                                         Heap Fetches: 1486503108
               ->  Index Scan using parameters_idx1 on parameters p  (cost=0.41..2.26 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=8111656)
                     Index Cond: ("PARAMETER_ID" = w."PARAMETER_ID")
         ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.28..1.05 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=8111656)
               Index Cond: ("SHIPMENT_ID" = w."SHIPMENT_ID")
               Heap Fetches: 9026419
   ->  Materialize  (cost=0.00..1730.37 rows=158 width=27) (actual time=0.000..0.032 rows=158 loops=8111656)
         ->  Seq Scan on pc  (cost=0.00..1729.58 rows=158 width=27) (actual time=0.007..6.734 rows=158 loops=1)
Planning time: 3.807 ms
Execution time: 8222351.640 ms
(30 rows)

Indexes:
    "waferdata_idx1" btree ("SHIPMENT_ID", "PARAMETER_ID")
    "waferdata_idx2" btree ("LASERMARK")

В общем, тут ничего особо не улучшить, как я понимаю пока .

...

Рейтинг:

0 / 0

04.10.2018, 14:12

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713030

Ы2

Участник

Сообщения: 233

Рейтинг: 0 / 0

rinace

Код: plaintext

1.
2.

Hash Cond: ((w."SHIPMENT_ID" = s."SHIPMENT_ID") AND ((SubPlan 1) = s."SHIPMENT_DATE"))
     ->  Seq Scan on wafer_data w  (cost=0.00..572942.21 rows=26829621 width=46) (actual time=0.007..11507.087 rows=26822469 loops=1)

Можно попробовать подумать, как обойтись без этих почти 27 миллиардов.

...

Рейтинг:

0 / 0

04.10.2018, 14:41

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713047

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

rinaceВ общем, тут ничего особо не улучшить, как я понимаю пока .

А какая версия базы у вас? Странно что параллельные запросы не включаются на десятке.

PS: я на вскидку вижу много вариантов чего тут улучшить можно но это уже за пределы поддержки на форуме выходит.
Если будет время вечером - предложу несколько вариантов. Но надо версию базы знать.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

04.10.2018, 15:07

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713103

rinace

Гость

Maxim BogukrinaceВ общем, тут ничего особо не улучшить, как я понимаю пока .

А какая версия базы у вас? Странно что параллельные запросы не включаются на десятке.

PS: я на вскидку вижу много вариантов чего тут улучшить можно но это уже за пределы поддержки на форуме выходит.
Если будет время вечером - предложу несколько вариантов. Но надо версию базы знать.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)

Вопрос не горящий, хотя с теоретической точки зрения интересно.
Если что подкинете, спасибо.

...

Рейтинг:

0 / 0

04.10.2018, 16:12

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713455

256k

Участник

Откуда: с.Торчилово, Псковская обл.

Сообщения: 426

Рейтинг: 0 / 0

Ы2rinace

Код: plaintext

1.
2.

Hash Cond: ((w."SHIPMENT_ID" = s."SHIPMENT_ID") AND ((SubPlan 1) = s."SHIPMENT_DATE"))
     ->  Seq Scan on wafer_data w  (cost=0.00..572942.21 rows=26829621 width=46) (actual time=0.007..11507.087 rows=26822469 loops=1)

Можно попробовать подумать, как обойтись без этих почти 27 миллиардов .

миллионов?

...

Рейтинг:

0 / 0

05.10.2018, 11:54

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713496

Ы2

Участник

Сообщения: 233

Рейтинг: 0 / 0

256k, действительно, миллионов. Но все равно многовато.

...

Рейтинг:

0 / 0

05.10.2018, 12:22

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713504

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Ы2256k, действительно, миллионов. Но все равно многовато.
таки до ярдов там доходило.
авторloops=1880840957

интересно, нельзя ли этот "макс по джойну" материализовать или в поле одной из (триггерно) или отдельной таблицей.

...

Рейтинг:

0 / 0

05.10.2018, 12:31

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713733

rinace

Гость

qwwqЫ2256k, действительно, миллионов. Но все равно многовато.
таки до ярдов там доходило.
авторloops=1880840957

интересно, нельзя ли этот "макс по джойну" материализовать или в поле одной из (триггерно) или отдельной таблицей.
Вот именно эта мысль и приходит в голову.
Нужно избавить от коррелированного подзапроса

...

Рейтинг:

0 / 0

05.10.2018, 16:50

| Ответить | Цитировать | Написать

Оптимизация запроса

#39713807

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

rinaceMaxim Bogukпропущено...

А какая версия базы у вас? Странно что параллельные запросы не включаются на десятке.

PS: я на вскидку вижу много вариантов чего тут улучшить можно но это уже за пределы поддержки на форуме выходит.
Если будет время вечером - предложу несколько вариантов. Но надо версию базы знать.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)

Вопрос не горящий, хотя с теоретической точки зрения интересно.
Если что подкинете, спасибо.

Тогда начнем с
show max_worker_processes ;
show max_parallel_workers_per_gather ;

select count(*) from wafer_data ;

explain analyze select distinct LASERMARK from wafer_data;

explain analyze select lasermark, max(shipment_date) from wafer_data join shipment on shipment.SHIPMENT_ID=wafer_data.SHIPMENT_ID group by lasermark;

А дальше будем думать.
Все с prod Базы.

PS: комментарии на счет структуры базы (в части отсутствия FK И наличия таблиц без primary key), структуры индексов (тут вообще один мат) и используемой версии базы ("хождение по багам") - я оставлю для отдельного комента. Если это у вас prod - очень очень грустно. :(

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

05.10.2018, 20:26

| Ответить | Цитировать | Написать

Оптимизация запроса

#39714496

rinace

Гость

Maxim Boguk,

Взято с prod:
version
select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

show max_worker_processes ;
max_worker_processes
----------------------
8
(1 row)

show max_parallel_workers_per_gather ;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)

select count(*) from wafer_data ;
count
----------
30608037
(1 row)

explain analyze select distinct "LASERMARK" from wafer_data;

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------- Unique  (cost=6029041.53..6181990.07 rows=486653 width=12) (actual time=174676.986..206832.689 rows=866649 loops=1)
   ->  Sort  (cost=6029041.53..6105515.80 rows=30589708 width=12) (actual time=174676.983..199167.535 rows=30608037 loops=1)
         Sort Key: "LASERMARK"
         Sort Method: external merge  Disk: 686840kB
         ->  Seq Scan on wafer_data  (cost=0.00..657418.08 rows=30589708 width=12) (actual time=0.007..26158.103 rows=30608037 loops=1)
 Planning time: 0.109 ms
 Execution time: 207105.820 ms
(7 rows)

explain analyze select "LASERMARK", max("SHIPMENT_DATE") from wafer_data join shipment on shipment."SHIPMENT_ID"=wafer_data."SHIPMENT_ID" group by "LASERMARK";

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

                                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------- Finalize GroupAggregate  (cost=2668147.65..2890683.74 rows=486653 width=20) (actual time=101139.189..124685.166 rows=866649 loops=1)
   Group Key: wafer_data."LASERMARK"
   ->  Gather Merge  (cost=2668147.65..2880950.68 rows=973306 width=20) (actual time=101139.181..123464.618 rows=1957735 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=2667147.63..2767607.00 rows=486653 width=20) (actual time=95264.153..114130.734 rows=652578 loops=3)
               Group Key: wafer_data."LASERMARK"
               ->  Sort  (cost=2667147.63..2699011.91 rows=12745712 width=20) (actual time=95264.136..108380.656 rows=10202679 loops=3)
                     Sort Key: wafer_data."LASERMARK"
                     Sort Method: external merge  Disk: 344904kB
                     ->  Hash Join  (cost=301.83..640156.01 rows=12745712 width=20) (actual time=3.628..20386.133 rows=10202679 loops=3)
                           Hash Cond: (wafer_data."SHIPMENT_ID" = shipment."SHIPMENT_ID")
                           ->  Parallel Seq Scan on wafer_data  (cost=0.00..478978.12 rows=12745712 width=16) (actual time=0.012..12470.861 rows=10202679 loops=3)
                           ->  Hash  (cost=265.16..265.16 rows=2934 width=12) (actual time=3.580..3.580 rows=2934 loops=3)
                                 Buckets: 4096  Batches: 1  Memory Usage: 159kB
                                 ->  Index Only Scan using shipment_idx1 on shipment  (cost=0.28..265.16 rows=2934 width=12) (actual time=0.025..1.283 rows=2934 loops=3)
                                       Heap Fetches: 1477
 Planning time: 0.558 ms
 Execution time: 125054.540 ms
(19 rows)

По поводу избавления от коррелированного подзапроса, удалось очень сильно снизить стоимость и соответственно уменьшить время выполнения :

Код: 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.

EXPLAIN ANALYZE SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from "SHIPMENT_DATE") AS year,
            extract(month from "SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."SOITECPARAM_NAME" AS soitecparam_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM wafer_data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
                          INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
                          INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
                          INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
                          INNER JOIN (
                                         SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "MAX_SHIPMENT_DATE"
                                         FROM shipment s2 INNER JOIN wafer_data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID" 
                                         WHERE 
                                         s2."SHIPMENT_DATE" >= '2018-07-01' AND s2."SHIPMENT_DATE" <= '2018-09-30'
                                         group by w2."LASERMARK") md 
                                ON md."LASERMARK" = w."LASERMARK" AND s."SHIPMENT_DATE" = md."MAX_SHIPMENT_DATE";

Код: 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.

                                                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1429148.18..3242976.17 rows=12492 width=135) (actual time=60613.436..128181.539 rows=10556392 loops=1)
   Hash Cond: (w."PARAMETER_ID" = p."PARAMETER_ID")
   ->  Hash Join  (cost=1426437.26..3240031.02 rows=12492 width=91) (actual time=60593.635..119708.429 rows=10556392 loops=1)
         Hash Cond: ((w."SHIPMENT_ID" = s."SHIPMENT_ID") AND ((max(s2."SHIPMENT_DATE")) = s."SHIPMENT_DATE"))
         ->  Hash Join  (cost=1422513.46..3006575.82 rows=30587532 width=54) (actual time=60583.958..113090.364 rows=10864501 loops=1)
               Hash Cond: ((w."LASERMARK")::text = (w2."LASERMARK")::text)
               ->  Seq Scan on wafer_data w  (cost=0.00..657371.32 rows=30587532 width=46) (actual time=0.006..24740.185 rows=30606087 loops=1)
               ->  Hash  (cost=1413578.30..1413578.30 rows=486653 width=20) (actual time=60545.452..60545.452 rows=283854 loops=1)
                     Buckets: 65536  Batches: 8  Memory Usage: 2429kB
                     ->  Finalize GroupAggregate  (cost=1251405.13..1408711.77 rows=486653 width=20) (actual time=51414.586..60147.524 rows=283854 loops=1)
                           Group Key: w2."LASERMARK"
                           ->  Gather Merge  (cost=1251405.13..1398978.71 rows=973306 width=20) (actual time=51414.557..59715.520 rows=641989 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Partial GroupAggregate  (cost=1250405.10..1285635.02 rows=486653 width=20) (actual time=51304.346..58185.640 rows=213996 loops=3)
                                       Group Key: w2."LASERMARK"
                                       ->  Sort  (cost=1250405.10..1260526.23 rows=4048452 width=20) (actual time=51304.297..55973.902 rows=3618595 loops=3)
                                             Sort Key: w2."LASERMARK"
                                             Sort Method: external merge  Disk: 121912kB
                                             ->  Hash Join  (cost=246.32..640054.98 rows=4048452 width=20) (actual time=26.372..29981.022 rows=3618595 loops=3)
                                                   Hash Cond: (w2."SHIPMENT_ID" = s2."SHIPMENT_ID")
                                                   ->  Parallel Seq Scan on wafer_data w2  (cost=0.00..478944.05 rows=12744805 width=16) (actual time=0.011..24766.025 rows=10202029 loops=3)
                                                   ->  Hash  (cost=234.67..234.67 rows=932 width=12) (actual time=0.899..0.899 rows=938 loops=3)
                                                         Buckets: 1024  Batches: 1  Memory Usage: 49kB
                                                         ->  Index Only Scan using shipment_idx1 on shipment s2  (cost=0.28..234.67 rows=932 width=12) (actual time=0.065..0.577 rows=938 loops=3)
                                                               Index Cond: (("SHIPMENT_DATE" >= '2018-07-01 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-09-30 00:00:00'::timestamp without time zone))
                                                               Heap Fetches: 813
         ->  Hash  (cost=3879.72..3879.72 rows=2938 width=57) (actual time=9.630..9.630 rows=2938 loops=1)
               Buckets: 4096  Batches: 1  Memory Usage: 301kB
               ->  Hash Join  (cost=1937.94..3879.72 rows=2938 width=57) (actual time=3.165..8.327 rows=2938 loops=1)
                     Hash Cond: (sp."PC_ID" = pc."PC_ID")
                     ->  Hash Join  (cost=192.22..2093.61 rows=2938 width=38) (actual time=1.801..5.468 rows=2938 loops=1)
                           Hash Cond: (s."SHIPMENT_ID" = sp."SHIPMENT_ID")
                           ->  Seq Scan on shipment s  (cost=0.00..1857.34 rows=2934 width=30) (actual time=0.004..1.950 rows=2934 loops=1)
                           ->  Hash  (cost=155.50..155.50 rows=2938 width=8) (actual time=1.775..1.775 rows=2938 loops=1)
                                 Buckets: 4096  Batches: 1  Memory Usage: 147kB
                                 ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.28..155.50 rows=2938 width=8) (actual time=0.033..0.900 rows=2938 loops=1)
                                       Heap Fetches: 464
                     ->  Hash  (cost=1743.65..1743.65 rows=165 width=27) (actual time=1.352..1.352 rows=165 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 19kB
                           ->  Seq Scan on pc  (cost=0.00..1743.65 rows=165 width=27) (actual time=0.007..1.288 rows=165 loops=1)
   ->  Hash  (cost=2424.30..2424.30 rows=22930 width=32) (actual time=19.653..19.653 rows=22930 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 1704kB
         ->  Seq Scan on parameters p  (cost=0.00..2424.30 rows=22930 width=32) (actual time=0.030..10.550 rows=22930 loops=1)
 Planning time: 3.233 ms
 Execution time: 130291.250 ms

По поводу структуры базы - ну в таком виде принята на поддержку, как раз и стоит задача привести все в нормальное состояние.
Как раз планировал посмотреть , что там с индексами и PK, FK.
По поводу версии - предлагаете на 10.5 ?

...

Рейтинг:

0 / 0

08.10.2018, 15:56

| Ответить | Цитировать | Написать

Оптимизация запроса

#39715071

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

rinace,

1)по версии - да 10.5
2)у вас там походу такие настройки что параллельное выполнение не хочет включаться (хотя надо смотреть конечно)

3)правильно ли я понимаю что lasermark в wafer_data уникальный? И если да то запрос мягко говоря странный получается.

4)ваш запрос неверный в части
SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "MAX_SHIPMENT_DATE"
FROM shipment s2 INNER JOIN wafer_data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
WHERE
s2."SHIPMENT_DATE" >= '2018-07-01' AND s2."SHIPMENT_DATE" <= '2018-09-30'
group by w2."LASERMARK"
он никак не будет проверять что МАКСИМАЛЬНЫЙ shipment_date для заданной LASERMARK находится в заданном диапазоне
(если предполагать что он все таки не уникальный конечно)

5)в таких запросах надо начинать с получения точной постановки чего именно от него хотят так как в половине случаев то что хотят и то что написано в запросе - вещи несовпадающие.

PS: ощущение что у вас много времени тратиться на работу с дисками... а тогда стабильной скорости не будет и надо смотреть что там с этой проблемой тоже.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

...

Рейтинг:

0 / 0

09.10.2018, 17:54

| Ответить | Цитировать | Написать

Оптимизация запроса

#39715301

Sergei.Agalakov

Участник

Сообщения: 575

Рейтинг: 0 / 0

rinace,
Максим, как обычно, дело говорит. Я правильно понимаю, что вы тянете все детали для последнего shipment в предоставленном диапазоне дат? Меня сильно смущает сакральный смысл WAFER_DATA.LASERMARK.

...

Рейтинг:

0 / 0

10.10.2018, 03:44

| Ответить | Цитировать | Написать

Оптимизация запроса

#39715414

rinace

Гость

Sergei.Agalakovrinace,
Максим, как обычно, дело говорит. Я правильно понимаю, что вы тянете все детали для последнего shipment в предоставленном диапазоне дат? Меня сильно смущает сакральный смысл WAFER_DATA.LASERMARK.

Спасибо Сергей,
Спасибо Максим.

Аналогичные мысли и мне приходили.
Смысл LASERMARK пока не понятен , как и смысл запроса.

Спасибо за наводки, анализирую.

...

Рейтинг:

0 / 0

10.10.2018, 10:32

| Ответить | Цитировать | Написать

Оптимизация запроса

#39715417

rinace

Гость

Maxim Bogukrinace,

4)ваш запрос неверный в части
SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "MAX_SHIPMENT_DATE"
FROM shipment s2 INNER JOIN wafer_data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
WHERE
s2."SHIPMENT_DATE" >= '2018-07-01' AND s2."SHIPMENT_DATE" <= '2018-09-30'
group by w2."LASERMARK"
он никак не будет проверять что МАКСИМАЛЬНЫЙ shipment_date для заданной LASERMARK находится в заданном диапазоне
(если предполагать что он все таки не уникальный конечно)

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
Отдельное спасибо за уточнение.
Стало быть попытка избавится от коррелированного подзапроса, пока не удачная оказалась.
Продолжу искать

...

Рейтинг:

0 / 0

10.10.2018, 10:33

| Ответить | Цитировать | Написать

Оптимизация запроса

#39717419

rinace

Гость

Maxim Bogukrinace,

3)правильно ли я понимаю что lasermark в wafer_data уникальный? И если да то запрос мягко говоря странный получается.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru

Нет поле LASERMARK не уникальное, только индексировано

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

\d wafer_data
                 Table "public.wafer_data"
        Column         |         Type          | Modifiers
-----------------------+-----------------------+-----------
 RUN_ID                | integer               |
 LASERMARK             | character varying(20) | not null
 LOTID                 | character varying(80) |
 SHIPMENT_ID           | integer               | not null
 PARAMETER_ID          | integer               | not null
 SOITEC_INTERNAL_VALUE | character varying(75) |
 REPORTED_VALUE        | character varying(75) |
 LOWER_SPEC_LIMIT      | numeric               |
 UPPER_SPEC_LIMIT      | numeric               |
Indexes:
    "waferdata_idx1" btree ("SHIPMENT_ID", "PARAMETER_ID")
    "waferdata_idx2" btree ("LASERMARK")
    "waferdata_idx3" btree ("SHIPMENT_ID")

...

Рейтинг:

0 / 0

15.10.2018, 10:30

| Ответить | Цитировать | Написать

Оптимизация запроса

#39717640

rinace

Гость

Очередная попытка оптимизировать запрос , избавившись от коррелированного подзапроса
На продакшн
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

Код: 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.

EXPLAIN ANALYZE SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from s."SHIPMENT_DATE") AS year,
            extract(month from s."SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."SOITECPARAM_NAME" AS soitecparam_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM wafer_data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
             INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
             INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
             INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
             INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
                          FROM shipment s2 INNER JOIN wafer_data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID" 
                          GROUP BY w2."LASERMARK"
                         ) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
        WHERE 
              s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;

В результате получен следующий план :

Код: 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.

--------------------------------QUERY PLAN-------------------------------------------
 Hash Join  (cost=2986342.12..4256439.92 rows=3920 width=136) (actual time=137476.728..205877.472 rows=10556392 loops=1)
   Hash Cond: (sp."PC_ID" = pc."PC_ID")
   ->  Hash Join  (cost=2984596.32..4254620.62 rows=3920 width=100) (actual time=137475.154..197789.686 rows=10556392 loops=1)
         Hash Cond: (w."PARAMETER_ID" = p."PARAMETER_ID")
         ->  Hash Join  (cost=2981877.38..4251847.78 rows=3920 width=72) (actual time=137454.821..191620.617 rows=10556392 loops=1)
               Hash Cond: ((s."SHIPMENT_DATE" = md."SHIPMENT_DATE") AND ((w."LASERMARK")::text = (md."LASERMARK")::text))
               ->  Hash Join  (cost=1288.75..891135.07 rows=9751743 width=72) (actual time=4.510..40927.367 rows=10855785 loops=1)
                      Hash Cond: (w."SHIPMENT_ID" = s."SHIPMENT_ID")
                     ->  Seq Scan on wafer_data w  (cost=0.00..674659.54 rows=31391954 width=46) (actual time=0.604..28389.134 rows=31340329 loops=1)
                      ->  Hash  (cost=1277.20..1277.20 rows=924 width=38) (actual time=3.883..3.883 rows=938 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 76kB
                           ->  Hash Join  (cost=211.50..1277.20 rows=924 width=38) (actual time=2.195..3.517 rows=938 loops=1)
                                 Hash Cond: (s."SHIPMENT_ID" = sp."SHIPMENT_ID")
                                 ->  Index Scan using shipment_idx2 on shipment s  (cost=0.28..1052.13 rows=922 width=30) (actual time=0.040..0.801 rows=938 loops=1)
                                       Index Cond: (("SHIPMENT_DATE" >= '2018-07-01 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-09-30 00:00:00'::timestamp without time zone))
                                 ->  Hash  (cost=173.92..173.92 rows=2984 width=8) (actual time=2.128..2.128 rows=2984 loops=1)
                                       Buckets: 4096  Batches: 1  Memory Usage: 149kB
                                       ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.28..173.92 rows=2984 width=8) (actual time=0.037..1.240 rows=2984 loops=1)
                                             Heap Fetches: 1281
               ->  Hash  (cost=2970436.84..2970436.84 rows=486653 width=20) (actual time=137449.841..137449.841 rows=886117 loops=1)
                     Buckets: 65536 (originally 65536)  Batches: 16 (originally 8)  Memory Usage: 3585kB
                     ->  Subquery Scan on md  (cost=2740527.20..2970436.84 rows=486653 width=20) (actual time=111355.474..136467.728 rows=886117 loops=1)
                           ->  Finalize GroupAggregate  (cost=2740527.20..2965570.31 rows=486653 width=20) (actual time=111355.472..135928.813 rows=886117 loops=1)
                                 Group Key: w2."LASERMARK"
                                 ->  Gather Merge  (cost=2740527.20..2955837.25 rows=973306 width=20) (actual time=111355.454..134634.157 rows=1994549 loops=1)
                                       Workers Planned: 2
                                       Workers Launched: 2
                                       ->  Partial GroupAggregate  (cost=2739527.18..2842493.56 rows=486653 width=20) (actual time=110667.344..130778.799 rows=664850 loops=3)
                                             Group Key: w2."LASERMARK"
                                             ->  Sort  (cost=2739527.18..2772227.13 rows=13079981 width=20) (actual time=110667.326..124536.284 rows=10446776 loops=3)
                                                   Sort Key: w2."LASERMARK"
                                                   Sort Method: external merge  Disk: 336216kB
                                                   ->  Hash Join  (cost=294.80..656930.37 rows=13079981 width=20) (actual time=2.416..23417.865 rows=10446776 loops=3)
                                                          Hash Cond: (w2."SHIPMENT_ID" = s2."SHIPMENT_ID")
                                                         ->  Parallel Seq Scan on wafer_data w2  (cost=0.00..491539.81 rows=13079981 width=16) (actual time=0.014..15402.169 rows=10446776 loops=3) 
                                                         ->  Hash  (cost=257.60..257.60 rows=2976 width=12) (actual time=2.366..2.366 rows=2976 loops=3)
                                                               Buckets: 4096  Batches: 1  Memory Usage: 160kB
                                                               ->  Index Only Scan using shipment_idx1 on shipment s2  (cost=0.28..257.60 rows=2976 width=12) (actual time=0.023..1.319 rows=2976 loops=3)
                                                                     Heap Fetches: 1546
         ->  Hash  (cost=2427.86..2427.86 rows=23286 width=32) (actual time=20.201..20.201 rows=23286 loops=1)
               Buckets: 32768  Batches: 1  Memory Usage: 1730kB
               ->  Seq Scan on parameters p  (cost=0.00..2427.86 rows=23286 width=32) (actual time=0.028..10.692 rows=23286 loops=1)
   ->  Hash  (cost=1743.69..1743.69 rows=169 width=28) (actual time=1.543..1.543 rows=169 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 19kB
         ->  Seq Scan on pc  (cost=0.00..1743.69 rows=169 width=28) (actual time=0.006..1.478 rows=169 loops=1)
 Planning time: 3.192 ms
 Execution time: 208014.134 ms
(47 rows)

В принципе , если не ошибся с изменением запроса неплохо.
Однако не понятно почему возникают Seq Scan on wafer_data

Таблица wafer_data проиндексирована по SHIPMENT_ID

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

 \d wafer_data
                 Table "public.wafer_data"
        Column         |         Type          | Modifiers
-----------------------+-----------------------+-----------
 RUN_ID                | integer               |
 LASERMARK             | character varying(20) | not null
 LOTID                 | character varying(80) |
 SHIPMENT_ID           | integer               | not null
 PARAMETER_ID          | integer               | not null
 SOITEC_INTERNAL_VALUE | character varying(75) |
 REPORTED_VALUE        | character varying(75) |
 LOWER_SPEC_LIMIT      | numeric               |
 UPPER_SPEC_LIMIT      | numeric               |
Indexes:
    "waferdata_idx1" btree ("SHIPMENT_ID", "PARAMETER_ID")
    "waferdata_idx2" btree ("LASERMARK")
    "waferdata_idx3" btree ("SHIPMENT_ID")

Это особенности версии 10.4 или можно заставить оптимизатор использовать индекс ?

...

Рейтинг:

0 / 0

15.10.2018, 14:04

| Ответить | Цитировать | Написать

Оптимизация запроса

#39717644

rinace

Гость

rinace,

Хотя с другой стороны внимательнее посмотрев условия запроса - слишком большая выборка получается rows=10855785
Может быть оптимизатор все делает так как и должен делать.
Нет смысла подключать индекс если в результате выбираются все строки.

Но хотелось бы конечно получить опровержение или подтверждение от более опытных коллег.

...

Рейтинг:

0 / 0

15.10.2018, 14:10

| Ответить | Цитировать | Написать

Оптимизация запроса

#39717656

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

rinace,

Так и есть. В данном случае SeqScan выгоднее, чем по индексу 10M раз тыкаться.

...

Рейтинг:

0 / 0

15.10.2018, 14:26

| Ответить | Цитировать | Написать

25 сообщений из 33, страница 1 из 2

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39715414&tid=1995441]:	0ms
get settings:	10ms
get forum list:	15ms
check forum access:	3ms
check topic access:	3ms
track hit:	66ms
get topic data:	8ms
get forum data:	2ms
get page messages:	63ms
get tp. blocked users:	1ms
others:	16ms

total:	187ms