现在我们有了执行计划,你分析一下哪慢:QUERY PLAN
Limit (cost=9550.97..9800.70 rows=1 width=38) (actual time=9795.278..9795.400 rows=1 loops=1)
-> Nested Loop (cost=9550.97..9800.70 rows=1 width=38) (actual time=9795.275..9795.396 rows=1 loops=1)
Join Filter: (((pm.original_store_code)::text = (pm_1.original_store_code)::text) AND ((ym.group_number)::text = (ym_1.group_number)::text) AND ((ym.host_cycle_code)::text = (ym_1.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ym_1.store_cycle_code)::text))
-> Group (cost=4704.81..4841.78 rows=1 width=29) (actual time=3247.129..3247.199 rows=1 loops=1)
Group Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code
-> Gather Merge (cost=4704.81..4841.77 rows=1 width=29) (actual time=3247.128..3247.196 rows=1 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Incremental Sort (cost=3704.80..3841.65 rows=2 width=29) (actual time=1623.114..1623.117 rows=1 loops=2)
Sort Key: pm.original_store_code, ym.group_number, jtm.staff_code, ym.host_cycle_code, ym.store_cycle_code
Presorted Key: pm.original_store_code
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 45kB Peak Memory: 45kB
Worker 0: Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> Nested Loop (cost=3568.02..3841.56 rows=1 width=29) (actual time=1622.343..1622.884 rows=131 loops=2)
-> Merge Left Join (cost=3567.74..3628.53 rows=522 width=21) (actual time=1621.903..1622.179 rows=228 loops=2)
Merge Cond: (((pm.original_store_code)::text = (ymk.original_store_code)::text) AND ((ym.host_cycle_code)::text = (ymk.host_cycle_code)::text) AND ((ym.store_cycle_code)::text = (ymk.store_cycle_code)::text) AND ((ym.information_category_code)::text = (ymk.information_category_code)::text))
Filter: ((((ymk.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk.apply_start_date) AND ('2023-11-09'::date <= ymk.apply_end_date)) OR (ymk.information_category_code IS NULL))
-> Sort (cost=3541.22..3552.58 rows=4542 width=21) (actual time=1621.878..1622.115 rows=228 loops=2)
Sort Key: pm.original_store_code, ym.host_cycle_code, ym.store_cycle_code, ym.information_category_code
Sort Method: external merge Disk: 13800kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Merge Join (cost=3008.64..3265.32 rows=4542 width=21) (actual time=29.380..351.880 rows=226995 loops=2)
Merge Cond: (((ym.pattern_type)::text = (pm.pattern_type)::text) AND ((ym.pattern_code)::text = (pm.pattern_code)::text))
-> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=26.306..27.871 rows=15434 loops=2)
Sort Key: ym.pattern_type, ym.pattern_code
Sort Method: quicksort Memory: 3181kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym (cost=0.00..759.94 rows=18168 width=21) (actual time=0.018..5.019 rows=15443 loops=2)
Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text))
-> Sort (cost=963.39..988.39 rows=10000 width=14) (actual time=6.142..27.592 rows=455991 loops=1)
Sort Key: pm.pattern_type, pm.pattern_code
Sort Method: quicksort Memory: 853kB
-> Seq Scan on m_pattern_10010001 pm (cost=0.00..299.00 rows=10000 width=14) (actual time=0.034..2.930 rows=10000 loops=1)
Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text))
-> Sort (cost=26.52..27.32 rows=320 width=102) (actual time=0.041..0.042 rows=1 loops=1)
Sort Key: ymk.original_store_code, ymk.host_cycle_code, ymk.store_cycle_code, ymk.information_category_code
Sort Method: quicksort Memory: 25kB
-> Seq Scan on m_reading_number_by_store ymk (cost=0.00..13.20 rows=320 width=102) (actual time=0.025..0.025 rows=1 loops=1)
-> Index Only Scan using m_staff_by_information_order_pkey on m_staff_by_information_order jtm (cost=0.29..0.40 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=455)
Index Cond: ((original_store_code = (pm.original_store_code)::text) AND (host_cycle_code = (ym.host_cycle_code)::text) AND (store_cycle_code = (ym.store_cycle_code)::text) AND (information_category_code = (ym.information_category_code)::text))
Heap Fetches: 0
-> Group (cost=4846.16..4958.88 rows=1 width=29) (actual time=6548.141..6548.193 rows=1 loops=1)
Group Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, jtm_1.setting_date
-> Incremental Sort (cost=4846.16..4958.85 rows=2 width=29) (actual time=6548.139..6548.191 rows=1 loops=1)
Sort Key: pm_1.original_store_code, ym_1.group_number, ym_1.host_cycle_code, ym_1.store_cycle_code, ym_1.information_category_code, jtm_1.setting_date
Presorted Key: pm_1.original_store_code, ym_1.group_number
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 32kB Peak Memory: 32kB
-> Nested Loop Left Join (cost=4733.53..4958.76 rows=1 width=29) (actual time=6546.392..6547.850 rows=97 loops=1)
Filter: ((((ymk_1.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_1.apply_start_date) AND ('2023-11-09'::date <= ymk_1.apply_end_date)) OR (ymk_1.information_category_code IS NULL))
-> Nested Loop (cost=4733.38..4958.55 rows=1 width=29) (actual time=6546.377..6547.735 rows=97 loops=1)
-> Group (cost=4732.69..4897.53 rows=1 width=23) (actual time=6546.292..6546.369 rows=24 loops=1)
Group Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code
-> Gather Merge (cost=4732.69..4897.52 rows=1 width=23) (actual time=6546.290..6546.349 rows=64 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Incremental Sort (cost=3732.68..3897.40 rows=2 width=23) (actual time=3271.945..3271.949 rows=32 loops=2)
Sort Key: pm_2.original_store_code, ym_2.group_number, jtm_1.setting_date, jtm_1.host_cycle_code, jtm_1.store_cycle_code
Presorted Key: pm_2.original_store_code
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB
Pre-sorted Groups: 1 Sort Method: quicksort Average Memory: 45kB Peak Memory: 45kB
Worker 0: Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
-> Nested Loop (cost=3568.02..3897.31 rows=1 width=23) (actual time=3271.191..3271.688 rows=131 loops=2)
-> Merge Left Join (cost=3567.74..3628.53 rows=522 width=21) (actual time=3270.971..3271.034 rows=228 loops=2)
Merge Cond: (((pm_2.original_store_code)::text = (ymk_2.original_store_code)::text) AND ((ym_2.host_cycle_code)::text = (ymk_2.host_cycle_code)::text) AND ((ym_2.store_cycle_code)::text = (ymk_2.store_cycle_code)::text) AND ((ym_2.information_category_code)::text = (ymk_2.information_category_code)::text))
Filter: ((((ymk_2.group_number)::text !~~ '0%'::text) AND ('2023-11-09'::date >= ymk_2.apply_start_date) AND ('2023-11-09'::date <= ymk_2.apply_end_date)) OR (ymk_2.information_category_code IS NULL))
-> Sort (cost=3541.22..3552.58 rows=4542 width=21) (actual time=3270.944..3270.968 rows=228 loops=2)
Sort Key: pm_2.original_store_code, ym_2.host_cycle_code, ym_2.store_cycle_code, ym_2.information_category_code
Sort Method: external sort Disk: 15584kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Merge Join (cost=3008.64..3265.32 rows=4542 width=21) (actual time=118.146..277.069 rows=226995 loops=2)
Merge Cond: (((ym_2.pattern_type)::text = (pm_2.pattern_type)::text) AND ((ym_2.pattern_code)::text = (pm_2.pattern_code)::text))
-> Sort (cost=2045.25..2090.67 rows=18168 width=21) (actual time=115.483..212.185 rows=15434 loops=2)
Sort Key: ym_2.pattern_type, ym_2.pattern_code
Sort Method: quicksort Memory: 3181kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on m_reading_number_by_pattern_1109_036 ym_2 (cost=0.00..759.94 rows=18168 width=21) (actual time=0.004..3.696 rows=15443 loops=2)
Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '1109_036'::text))
-> Sort (cost=963.39..988.39 rows=10000 width=14) (actual time=5.316..32.792 rows=455991 loops=1)
Sort Key: pm_2.pattern_type, pm_2.pattern_code
Sort Method: quicksort Memory: 853kB
-> Seq Scan on m_pattern_10010001 pm_2 (cost=0.00..299.00 rows=10000 width=14) (actual time=0.027..2.123 rows=10000 loops=1)
Filter: (('2023-11-09'::date >= apply_start_date) AND ('2023-11-09'::date <= apply_end_date) AND ((version)::text = '10010001'::text))
-> Sort (cost=26.52..27.32 rows=320 width=102) (actual time=0.045..0.046 rows=1 loops=1)
Sort Key: ymk_2.original_store_code, ymk_2.host_cycle_code, ymk_2.store_cycle_code, ymk_2.information_category_code
Sort Method: quicksort Memory: 25kB
-> Seq Scan on m_reading_number_by_store ymk_2 (cost=0.00..13.20 rows=320 width=102) (actual time=0.024..0.025 rows=1 loops=1)
-> Index Scan using m_staff_by_information_order_pkey on m_staff_by_information_order jtm_1 (cost=0.29..0.50 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=455)
Index Cond: (((original_store_code)::text = (pm_2.original_store_code)::text) AND ((host_cycle_code)::text = (ym_2.host_cycle_code)::text) AND ((store_cycle_code)::text = (ym_2.store_cycle_code)::text) AND ((information_category_code)::text = (ym_2.information_category_code)::text))
-> Nested Loop (cost=0.70..60.96 rows=5 width=21) (actual time=0.034..0.056 rows=4 loops=24)
-> Index Scan using m_pattern_10010001_pkey on m_pattern_10010001 pm_1 (cost=0.29..15.41 rows=5 width=14) (actual time=0.004..0.005 rows=5 loops=24)
Index Cond: (((version)::text = '10010001'::text) AND ((original_store_code)::text = (pm_2.original_store_code)::text) AND (apply_start_date <= '2023-11-09'::date))
Filter: ('2023-11-09'::date <= apply_end_date)
-> Index Scan using m_reading_number_by_pattern_1109_036_pkey on m_reading_number_by_pattern_1109_036 ym_1 (cost=0.41..9.10 rows=1 width=21) (actual time=0.009..0.010 rows=1 loops=116)
Index Cond: (((pattern_type)::text = (pm_1.pattern_type)::text) AND ((pattern_code)::text = (pm_1.pattern_code)::text) AND (apply_start_date <= '2023-11-09'::date) AND ((host_cycle_code)::text = (jtm_1.host_cycle_code)::text) AND ((store_cycle_code)::text = (jtm_1.store_cycle_code)::text) AND ((group_number)::text = (ym_2.group_number)::text) AND ((version)::text = '1109_036'::text))
Filter: ('2023-11-09'::date <= apply_end_date)
-> Index Scan using m_reading_number_by_store_pkey on m_reading_number_by_store ymk_1 (cost=0.15..0.19 rows=1 width=102) (actual time=0.001..0.001 rows=0 loops=97)
Index Cond: (((original_store_code)::text = (pm_1.original_store_code)::text) AND ((host_cycle_code)::text = (ym_1.host_cycle_code)::text) AND ((store_cycle_code)::text = (ym_1.store_cycle_code)::text) AND ((information_category_code)::text = (ym_1.information_category_code)::text))
Planning Time: 6.857 ms
Execution Time: 9981.633 ms
SQL:-- explain(analyze,buffers,verbose)
EXPLAIN ANALYZE
WITH wk1 AS
(
SELECT
pm.original_store_code,
ym.group_number,
jtm.staff_code,
ym.host_cycle_code,
ym.store_cycle_code
FROM
m_pattern AS pm
INNER JOIN
m_reading_number_by_pattern AS ym
ON
pm.pattern_type = ym.pattern_type
AND pm.pattern_code = ym.pattern_code
AND ym.version = '1109_036'
INNER JOIN
m_staff_by_information_order AS jtm
ON
pm.original_store_code = jtm.original_store_code
AND ym.host_cycle_code = jtm.host_cycle_code
AND ym.store_cycle_code = jtm.store_cycle_code
AND ym.information_category_code = jtm.information_category_code
LEFT JOIN
m_reading_number_by_store AS ymk
ON
pm.original_store_code = ymk.original_store_code
AND ym.host_cycle_code = ymk.host_cycle_code
AND ym.store_cycle_code = ymk.store_cycle_code
AND ym.information_category_code = ymk.information_category_code
WHERE
pm.version = '10010001'
AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date )
OR ymk.information_category_code IS NULL )
AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date
AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date
GROUP BY
pm.original_store_code,
ym.group_number,
jtm.staff_code,
ym.host_cycle_code,
ym.store_cycle_code
),
WK2 AS
(
SELECT
pm.original_store_code,
ym.group_number,
jtm.setting_date,
jtm.host_cycle_code,
jtm.store_cycle_code
FROM
m_pattern AS pm
INNER JOIN
m_reading_number_by_pattern AS ym
ON
pm.pattern_type = ym.pattern_type
AND pm.pattern_code = ym.pattern_code
AND ym.version = '1109_036'
INNER JOIN
m_staff_by_information_order AS jtm
ON
pm.original_store_code = jtm.original_store_code
AND ym.host_cycle_code = jtm.host_cycle_code
AND ym.store_cycle_code = jtm.store_cycle_code
AND ym.information_category_code = jtm.information_category_code
LEFT JOIN
m_reading_number_by_store AS ymk
ON
pm.original_store_code = ymk.original_store_code
AND ym.host_cycle_code = ymk.host_cycle_code
AND ym.store_cycle_code = ymk.store_cycle_code
AND ym.information_category_code = ymk.information_category_code
WHERE
pm.version = '10010001'
AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date )
OR ymk.information_category_code IS NULL )
AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date
AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date
GROUP BY
pm.original_store_code,
ym.group_number,
jtm.setting_date,
jtm.host_cycle_code,
jtm.store_cycle_code
),
wk3 AS
(
SELECT
pm.original_store_code,
ym.group_number,
ym.host_cycle_code,
ym.store_cycle_code,
ym.information_category_code,
wk2.setting_date
FROM
m_pattern AS pm
INNER JOIN
m_reading_number_by_pattern AS ym
ON
pm.pattern_type = ym.pattern_type
AND pm.pattern_code = ym.pattern_code
AND ym.version = '1109_036'
INNER JOIN
wk2
ON
pm.original_store_code = wk2.original_store_code
AND ym.group_number = wk2.group_number
AND ym.host_cycle_code = wk2.host_cycle_code
AND ym.store_cycle_code = wk2.store_cycle_code
LEFT JOIN
m_reading_number_by_store AS ymk
ON
pm.original_store_code = ymk.original_store_code
AND ym.host_cycle_code = ymk.host_cycle_code
AND ym.store_cycle_code = ymk.store_cycle_code
AND ym.information_category_code = ymk.information_category_code
WHERE
pm.version = '10010001'
AND (( ymk.group_number NOT LIKE '0%' AND '2023-11-09 03:00:00' BETWEEN ymk.apply_start_date AND ymk.apply_end_date )
OR ymk.information_category_code IS NULL )
AND '2023-11-09 03:00:00' BETWEEN pm.apply_start_date AND pm.apply_end_date
AND '2023-11-09 03:00:00' BETWEEN ym.apply_start_date AND ym.apply_end_date
GROUP BY
pm.original_store_code,
ym.group_number,
ym.host_cycle_code,
ym.store_cycle_code,
ym.information_category_code,
wk2.setting_date
)
SELECT
wk1.original_store_code,
wk3.host_cycle_code,
wk3.store_cycle_code,
wk3.information_category_code,
wk1.staff_code,
wk3.setting_date
FROM
wk1
INNER JOIN
wk3
ON
wk1.original_store_code = wk3.original_store_code
AND wk1.group_number = wk3.group_number
AND wk1.host_cycle_code = wk3.host_cycle_code
AND wk1.store_cycle_code = wk3.store_cycle_code
limit 1
;