Оптимизируйте запрос, чтобы получить отдельные записи
У меня есть следующий запрос, чтобы получить количество проданных товаров.
Текущий запрос: который дает повторяющиеся записи.
--(322212 rows affected)
--00:00:08 Time Taken
SELECT pc.qty_typecode ,
pc.prdcost, AS prdcost ,
pc.d1cost, AS d1cost ,
pc.d2cost, AS d2cost ,
pc.saledate
,COUNT(ps.pdt_soldsID) OVER ( PARTITION BY pc.saledate, qt.qtycvalue) AS pdt_soldsCOUNT
FROM prdcost pc
left JOIN qty_type qt ON qt.qty_typecode = pc.qty_typecode and qt.classcode = pc.classcode
left JOIN pdt_solds ps ON pc.qty_typecode = ps.qty_typecode and pc.classcode = ps.classcode and pc.saledate = ps.saledate
WHERE pc.classcode = 'CD901';
План выполнения:
WindowAgg (cost=47.12..34684.67 rows=212179 width=90) (actual time=5.950..7402.059 rows=322212 loops=1)
-> Incremental Sort (cost=47.12..30971.54 rows=212179 width=33) (actual time=5.904..5850.861 rows=322212 loops=1)
Sort Key: pc.saledate, qt.qtycvalue
Presorted Key: pc.saledate
Full-sort Groups: 544 Sort Method: quicksort Average Memory: 28kB Peak Memory: 28kB
Pre-sorted Groups: 511 Sort Method: quicksort Average Memory: 308kB Peak Memory: 331kB
-> Nested Loop Left Join (cost=1.00..13668.98 rows=212179 width=33) (actual time=1.285..3704.330 rows=322212 loops=1)
-> Nested Loop Left Join (cost=0.57..1698.93 rows=13345 width=36) (actual time=1.203..187.022 rows=13345 loops=1)
-> Index Scan using pk_prdcost96e969d29d073e188a438d9bb770e4ae on prdcost pc (cost=0.29..1329.04 rows=13345 width=31) (actual time=1.131..43.352 rows=13345 loops=1)
Index Cond: ((classcode)::"varchar" = 'CD901'::"varchar")
-> Memoize (cost=0.28..0.30 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=13345)
Cache Key: pc.classcode, pc.qty_typecode
Cache Mode: logical
Hits: 13316 Misses: 29 Evictions: 0 Overflows: 0 Memory Usage: 4kB
-> Index Only Scan using ix_qty_typeqty_type4e1c5ab4d14a7b3776609e998f02177e on qty_type qt (cost=0.27..0.29 rows=1 width=17) (actual time=0.042..0.042 rows=1 loops=29)
Index Cond: ((classcode = (pc.classcode)::"varchar") AND (classcode = 'CD901'::"varchar") AND (qty_typecode = (pc.qty_typecode)::"varchar"))
Heap Fetches: 10
-> Memoize (cost=0.43..4.37 rows=15 width=23) (actual time=0.191..0.259 rows=28 loops=13345)
Cache Key: pc.classcode, pc.qty_typecode, pc.saledate
Cache Mode: binary
Hits: 0 Misses: 13345 Evictions: 10092 Overflows: 0 Memory Usage: 4097kB
-> Index Scan using ix_pdt_solds0ff36731c0b25757fbb5620cf6962faa on pdt_solds ps (cost=0.42..4.36 rows=15 width=23) (actual time=0.189..0.248 rows=28 loops=13345)
Index Cond: ((saledate = pc.saledate) AND ((classcode)::"varchar" = (pc.classcode)::"varchar") AND ((classcode)::"varchar" = 'CD901'::"varchar") AND ((qty_typecode)::"varchar" = (pc.qty_typecode)::"varchar"))
Planning Time: 21.895 ms
Execution Time: 8395.226 ms
Примечание : Таблицаpdt_soldsимеет повторяющиеся записи с отдельным столбцом идентификаторовpdt_soldsID.
Чтобы уменьшить количество повторяющихся записей, я переписал запрос, как показано ниже:
Переписан запрос: для уменьшения повторяющихся записей.
--(14941 rows affected)
--00:00:18 Time Taken
with cte as
(
select classcode, saledate, qty_typecode
,COUNT(pdt_soldsID) pdt_soldsID
from pdt_solds
WHERE classcode = 'CD901'
GROUP BY classcode, saledate, qty_typecode
)
SELECT pc.qty_typecode,
pc.prdcost, AS prdcost,
pc.d1cost, AS d1cost,
pc.d2cost, AS d2cost,
pc.saledate
, qt.qtycvalue
, SUM(t.pdt_soldsID) OVER (PARTITION BY pc.saledate, qt.qtycvalue) AS pdt_soldsCOUNT
FROM prdcost pc
LEFT JOIN qty_type qt ON pc.qty_typecode = qt.qty_typecode AND pc.classcode = qt.classcode
LEFT JOIN cte t
ON pc.classcode = t.classcode and pc.saledate = T.saledate and pc.qty_typecode = t.qty_typecode
WHERE pc.classcode = 'CD901';
План выполнения:
WindowAgg (cost=51338.54..51605.44 rows=14941 width=90) (actual time=16994.585..17051.169 rows=14941 loops=1)
-> Sort (cost=51338.54..51371.90 rows=14941 width=33) (actual time=16994.537..16996.465 rows=14941 loops=1)
Sort Key: pc.saledate, rt.qtycvalue
Sort Method: quicksort Memory: 1427kB
-> Nested Loop Left Join (cost=34306.49..50424.14 rows=14941 width=33) (actual time=3328.237..16715.227 rows=14941 loops=1)
-> Hash Right Join (cost=34306.21..50054.25 rows=14941 width=35) (actual time=3328.142..16592.567 rows=14941 loops=1)
Hash Cond: (((pdt_solds.classcode)::"varchar" = (pc.classcode)::"varchar") AND ((pdt_solds.qty_typecode)::"varchar" = (pc.qty_typecode)::"varchar"))
Join Filter: (pc.saledate = pdt_solds.saledate)
Rows Removed by Join Filter: 2726548
-> Finalize GroupAggregate (cost=33553.59..48447.29 rows=56023 width=23) (actual time=2363.688..2534.878 rows=9643 loops=1)
Group Key: pdt_solds.classcode, pdt_solds.saledate, pdt_solds.qty_typecode
-> Gather Merge (cost=33553.59..46626.54 rows=112046 width=27) (actual time=2363.662..2473.792 rows=16560 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=32553.57..32693.63 rows=56023 width=27) (actual time=2277.771..2278.778 rows=5520 loops=3)
Sort Key: pdt_solds.saledate, pdt_solds.qty_typecode
Sort Method: quicksort Memory: 660kB
Worker 0: Sort Method: quicksort Memory: 648kB
Worker 1: Sort Method: quicksort Memory: 564kB
-> Partial HashAggregate (cost=25571.03..28135.11 rows=56023 width=27) (actual time=2123.986..2125.474 rows=5520 loops=3)
Group Key: pdt_solds.classcode, pdt_solds.saledate, pdt_solds.qty_typecode
Planned Partitions: 4 Batches: 1 Memory Usage: 1553kB
Worker 0: Batches: 1 Memory Usage: 1569kB
Worker 1: Batches: 1 Memory Usage: 1569kB
-> Parallel Seq Scan on pdt_solds (cost=0.00..12425.79 rows=170995 width=23) (actual time=0.551..518.896 rows=136878 loops=3)
Filter: ((classcode)::"varchar" = 'CD901'::"varchar")
Rows Removed by Filter: 106374
-> Hash (cost=552.44..552.44 rows=14941 width=31) (actual time=110.010..110.011 rows=14941 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 1011kB
-> Seq Scan on prdcost pc (cost=0.00..552.44 rows=14941 width=31) (actual time=1.162..44.535 rows=14941 loops=1)
Filter: ((classcode)::"varchar" = 'CD901'::"varchar")
Rows Removed by Filter: 7970
-> Memoize (cost=0.28..0.30 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=14941)
Cache Key: pc.classcode, pc.qty_typecode
Cache Mode: logical
Hits: 13316 Misses: 29 Evictions: 0 Overflows: 0 Memory Usage: 4kB
-> Index Only Scan using ix_qty_typeqty_type4e1c5ab4d14a7b3776609e998f02177e on qty_type rt (cost=0.27..0.29 rows=1 width=17) (actual time=0.044..0.045 rows=1 loops=29)
Index Cond: ((classcode = (pc.classcode)::"varchar") AND (classcode = 'CD901'::"varchar") AND (qty_typecode = (pc.qty_typecode)::"varchar"))
Heap Fetches: 10
Planning Time: 2.051 ms
Execution Time: 17109.260 ms
1 ответ
Ваш запрос имеет неправильный синтаксис. Ключевое слово AS не должно начинаться с запятой, перепишите его как:
WITH
cte AS
(SELECT classcode,
saledate,
qty_typecode,
COUNT(pdt_soldsID) pdt_soldsID
FROM pdt_solds
WHERE classcode = 'CD901'
GROUP BY classcode,
saledate,
qty_typecode)
SELECT pc.qty_typecode,
pc.prdcost AS prdcost,
pc.d1cost AS d1cost,
pc.d2cost AS d2cost,
pc.saledate,
qt.qtycvalue,
SUM(t.pdt_soldsID) OVER(PARTITION BY pc.saledate,
qt.qtycvalue) AS pdt_soldsCOUNT
FROM prdcost AS pc
LEFT JOIN qty_type AS qt
ON pc.qty_typecode = qt.qty_typecode
AND pc.classcode = qt.classcode
LEFT JOIN cte AS t
ON pc.classcode = t.classcode
AND pc.saledate = t.saledate
AND pc.qty_typecode = t.qty_typecode
WHERE pc.classcode = 'CD901';
Второе предложение WHERE глупо, потому что вы уже отфильтровали CTE по «classcode = 'CD901'». Поэтому удалите это предложение WHERE.
У вас именно такой индекс: ???
CREATE INDEX X001 ON pdt_solds (classcode, saledate, qty_typecode);
CREATE INDEX X002 ON prdcost (classcode, saledate, qty_typecode) INCLUDE (prdcost, d1cost, d2cost
CREATE INDEX X003 ON qty_type (qty_typecode, classcode) INCLUDE (qtycvalue);
Если нет, создайте их.