Коллеги,
похоже история близится к финалу.
Итак на тестовой базе 10.5 имеем улучшенный запрос (без коррелированного подзапроса)
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.
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_small 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_small 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-10-13' AND s."SHIPMENT_DATE" <= '2018-10-15' ;
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.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=140220.68..143047.49 rows=1 width=605) (actual time=13472.266..24412.460 rows=83280 loops=1)
Join Filter: (sp."PC_ID" = pc."PC_ID")
Rows Removed by Join Filter: 14074320
-> Nested Loop (cost=140220.68..143040.66 rows=1 width=543) (actual time=13472.242..20218.763 rows=83280 loops=1)
-> Nested Loop (cost=140220.39..143040.35 rows=1 width=489) (actual time=13472.223..19873.315 rows=83280 loops=1)
Join Filter: (s."SHIPMENT_ID" = w."SHIPMENT_ID")
Rows Removed by Join Filter: 90880
-> Merge Join (cost=140219.96..142470.06 rows=991 width=83) (actual time=13471.986..17735.570 rows=83240 loops=1)
Merge Cond: (sp."SHIPMENT_ID" = s."SHIPMENT_ID")
-> Index Only Scan using shipmentpc_idx1 on shipment_pc sp (cost=0.28..238.99 rows=3914 width=8) (actual time=0.183..2.347 rows=96 loops=1)
Heap Fetches: 96
-> Materialize (cost=140219.68..179610.34 rows=495 width=75) (actual time=13470.408..17665.276 rows=83201 loops=1)
-> Nested Loop (cost=140219.68..179609.10 rows=495 width=75) (actual time=13470.389..17626.127 rows=2081 loops=1)
Join Filter: (s."SHIPMENT_DATE" = md."SHIPMENT_DATE")
Rows Removed by Join Filter: 1205505
-> Index Scan using shipment_idx1 on shipment s (cost=0.28..70.05 rows=2 width=60) (actual time=0.073..1.948 rows=2 loops=1)
Index Cond: (("SHIPMENT_DATE" >= '2018-10-13 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-10-15 00:00:00'::timestamp without time zone))
-> Materialize (cost=140219.41..163009.91 rows=495460 width=23) (actual time=6734.960..8726.741 rows=603793 loops=2)
-> Subquery Scan on md (cost=140219.41..157628.61 rows=495460 width=23) (actual time=13469.873..16979.084 rows=603793 loops=1)
-> GroupAggregate (cost=140219.41..152674.01 rows=495460 width=23) (actual time=13469.871..16852.028 rows=603793 loops=1)
Group Key: w2."LASERMARK"
-> Sort (cost=140219.41..142719.41 rows=1000000 width=23) (actual time=13469.852..16373.420 rows=1000000 loops=1)
Sort Key: w2."LASERMARK"
Sort Method: external merge Disk: 33288kB
-> Hash Join (cost=68.00..20051.56 rows=1000000 width=23) (actual time=4.545..687.091 rows=1000000 loops=1)
Hash Cond: (w2."SHIPMENT_ID" = s2."SHIPMENT_ID")
-> Seq Scan on wafer_data_small w2 (cost=0.00..17353.00 rows=1000000 width=19) (actual time=0.016..192.384 rows=1000000 loops=1)
-> Hash (cost=43.00..43.00 rows=2000 width=12) (actual time=4.499..4.499 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 102kB
-> Seq Scan on shipment s2 (cost=0.00..43.00 rows=2000 width=12) (actual time=0.008..3.833 rows=2000 loops=1)
-> Index Scan using wafer_data_small_idx2 on wafer_data_small w (cost=0.42..0.55 rows=2 width=433) (actual time=0.021..0.024 rows=2 loops=83240)
Index Cond: (("LASERMARK")::text = (md."LASERMARK")::text)
-> Index Scan using parameters_idx1 on parameters p (cost=0.29..0.31 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=83280)
Index Cond: ("PARAMETER_ID" = w."PARAMETER_ID")
-> Seq Scan on pc (cost=0.00..4.70 rows=170 width=54) (actual time=0.002..0.023 rows=170 loops=83280)
Planning time: 22.055 ms
Execution time: 24455.391 ms
Наиболее дорогая операция выделена.
Для улучшения запроса и исключение наиболее ресурсной операции , было предложено использовать
1.
2.
3.
4.
5.
CREATE MATERIALIZED VIEW LASERMARK_VIEW
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN wafer_data_small w ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
GROUP BY w."LASERMARK" ;
В результате запрос преобразовался до вида :
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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_small 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 LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-10-13' AND s."SHIPMENT_DATE" <= '2018-10-15' ;
C планом выполнения :
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.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.27..1838.24 rows=1 width=605) (actual time=0.697..7698.010 rows=83280 loops=1)
Join Filter: (sp."PC_ID" = pc."PC_ID")
Rows Removed by Join Filter: 14074320
-> Nested Loop (cost=1.27..1831.41 rows=1 width=543) (actual time=0.666..3287.672 rows=83280 loops=1)
-> Nested Loop (cost=0.98..1831.10 rows=1 width=489) (actual time=0.650..2898.555 rows=83280 loops=1)
Join Filter: (s."SHIPMENT_ID" = w."SHIPMENT_ID")
Rows Removed by Join Filter: 90880
-> Merge Join (cost=0.56..1420.31 rows=662 width=126) (actual time=0.582..789.376 rows=83240 loops=1)
Merge Cond: (sp."SHIPMENT_ID" = s."SHIPMENT_ID")
-> Index Only Scan using shipmentpc_idx1 on shipment_pc sp (cost=0.28..238.99 rows=3914 width=8) (actual time=0.046..0.715 rows=96 loops=1)
Heap Fetches: 96
-> Materialize (cost=0.28..22646.90 rows=331 width=118) (actual time=0.349..723.487 rows=83201 loops=1)
-> Nested Loop (cost=0.28..22646.07 rows=331 width=118) (actual time=0.341..688.626 rows=2081 loops=1)
Join Filter: (s."SHIPMENT_DATE" = md."SHIPMENT_DATE")
Rows Removed by Join Filter: 1205505
-> Index Scan using shipment_idx1 on shipment s (cost=0.28..70.05 rows=2 width=60) (actual time=0.029..5.038 rows=2 loops=1)
Index Cond: (("SHIPMENT_DATE" >= '2018-10-13 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2018-10-15 00:00:00'::timestamp without time zone))
-> Seq Scan on lasermark_view md (cost=0.00..7153.56 rows=330756 width=66) (actual time=0.150..241.544 rows=603793 loops=2)
-> Index Scan using wafer_data_small_idx2 on wafer_data_small w (cost=0.42..0.60 rows=2 width=433) (actual time=0.021..0.023 rows=2 loops=83240)
Index Cond: (("LASERMARK")::text = (md."LASERMARK")::text)
-> Index Scan using parameters_idx1 on parameters p (cost=0.29..0.31 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=83280)
Index Cond: ("PARAMETER_ID" = w."PARAMETER_ID")
-> Seq Scan on pc (cost=0.00..4.70 rows=170 width=54) (actual time=0.003..0.024 rows=170 loops=83280)
Planning time: 15.196 ms
Execution time: 7712.509 ms
Т.е. запрос был улучшен в 3 раза.
Рефрешиться представление примерно 18 секунд. И учитывая специфику приложения не так часто будет требоваться.
Таким образом история начавшаяся с 2-х часов выполнения запроса, завершилась несколькими секундами.
Что тут еще можно протьюнит, уже фантазии не хватает.
Если кто подскажет, спасибо.
