如何优化带有连接查询的PostgreSQL查询？

Question

我编写了下面的查询，成本约为1770077元，我希望能够降低成本，请建议最佳方案。

SELECT  eco.operationalstatus,SI.storecode,count(SI.gustoreid), SI.storename,SI.storetype, PPK.guproductpkgid,PPK.code AS ProductCode, PPK.description as ProductName
    FROM inv_storeinformation as SI
    LEFT OUTER JOIN sms_ecotransaction as et  on SI.gustoreid=et.currentgustoreid
    LEFT OUTER JOIN sms_ecomaster as eco  on et.refid=eco.ecoid
    LEFT OUTER JOIN sms_productpackagemaster as PPK on eco.guproductid=PPK.guproductpkgid

    Group by eco.operationalstatus,SI.storecode,SI.gustoreid, SI.storename,SI.storetype, PPK.guproductpkgid,PPK.code , PPK.description
    order by SI.storecode,PPK.code

执行时，我得到了以下实际执行计划成本。

"GroupAggregate  (cost=1770077.23..1798321.49 rows=16836 width=661)"
"  ->  Sort  (cost=1770077.23..1772884.82 rows=1123036 width=661)"
"        Sort Key: si.storecode, ppk.code, eco.operationalstatus, si.gustoreid, si.storename, si.storetype, ppk.guproductpkgid, ppk.description"
"        ->  Hash Left Join  (cost=62583.69..336765.88 rows=1123036 width=661)"
"              Hash Cond: ((eco.guproductid)::text = (ppk.guproductpkgid)::text)"
"              ->  Hash Right Join  (cost=62577.94..321318.39 rows=1123036 width=634)"
"                    Hash Cond: ((et.currentgustoreid)::text = (si.gustoreid)::text)"
"                    ->  Hash Left Join  (cost=62576.43..305875.13 rows=1123036 width=39)"
"                          Hash Cond: ((et.refid)::text = (eco.ecoid)::text)"
"                          ->  Seq Scan on sms_ecotransaction et  (cost=0.00..24914.25 rows=779925 width=35)"
"                          ->  Hash  (cost=39803.30..39803.30 rows=1121130 width=40)"
"                                ->  Seq Scan on sms_ecomaster eco  (cost=0.00..39803.30 rows=1121130 width=40)"
"                    ->  Hash  (cost=1.23..1.23 rows=23 width=612)"
"                          ->  Seq Scan on inv_storeinformation si  (cost=0.00..1.23 rows=23 width=612)"
"              ->  Hash  (cost=4.22..4.22 rows=122 width=44)"
"                    ->  Seq Scan on sms_productpackagemaster ppk  (cost=0.00..4.22 rows=122 width=44)"

“EXPLAIN ANALYZE VERBOSE”是以下内容：

"GroupAggregate  (cost=1770077.23..1798321.49 rows=16836 width=661) (actual time=154832.599..184209.736 rows=83 loops=1)"
"  Output: eco.operationalstatus, si.storecode, count(si.gustoreid), si.storename, si.storetype, ppk.guproductpkgid, ppk.code, ppk.description, si.gustoreid"
"  ->  Sort  (cost=1770077.23..1772884.82 rows=1123036 width=661) (actual time=154830.264..183333.793 rows=1096170 loops=1)"
"        Output: eco.operationalstatus, si.storecode, si.storename, si.storetype, ppk.guproductpkgid, ppk.code, ppk.description, si.gustoreid"
"        Sort Key: si.storecode, ppk.code, eco.operationalstatus, si.gustoreid, si.storename, si.storetype, ppk.guproductpkgid, ppk.description"
"        Sort Method: external merge  Disk: 109184kB"
"        ->  Hash Left Join  (cost=62583.69..336765.88 rows=1123036 width=661) (actual time=988.629..4165.343 rows=1096170 loops=1)"
"              Output: eco.operationalstatus, si.storecode, si.storename, si.storetype, ppk.guproductpkgid, ppk.code, ppk.description, si.gustoreid"
"              Hash Cond: ((eco.guproductid)::text = (ppk.guproductpkgid)::text)"
"              ->  Hash Right Join  (cost=62577.94..321318.39 rows=1123036 width=634) (actual time=988.508..3582.942 rows=1096170 loops=1)"
"                    Output: si.storecode, si.storename, si.storetype, si.gustoreid, eco.operationalstatus, eco.guproductid"
"                    Hash Cond: ((et.currentgustoreid)::text = (si.gustoreid)::text)"
"                    ->  Hash Left Join  (cost=62576.43..305875.13 rows=1123036 width=39) (actual time=988.472..3056.651 rows=1121492 loops=1)"
"                          Output: et.currentgustoreid, eco.operationalstatus, eco.guproductid"
"                          Hash Cond: ((et.refid)::text = (eco.ecoid)::text)"
"                          ->  Seq Scan on public.sms_ecotransaction et  (cost=0.00..24914.25 rows=779925 width=35) (actual time=0.184..316.954 rows=779925 loops=1)"
"                                Output: et.currentgustoreid, et.refid"
"                          ->  Hash  (cost=39803.30..39803.30 rows=1121130 width=40) (actual time=981.649..981.649 rows=1121130 loops=1)"
"                                Output: eco.operationalstatus, eco.ecoid, eco.guproductid"
"                                Buckets: 2048  Batches: 128  Memory Usage: 645kB"
"                                ->  Seq Scan on public.sms_ecomaster eco  (cost=0.00..39803.30 rows=1121130 width=40) (actual time=0.006..471.218 rows=1121130 loops=1)"
"                                      Output: eco.operationalstatus, eco.ecoid, eco.guproductid"
"                    ->  Hash  (cost=1.23..1.23 rows=23 width=612) (actual time=0.026..0.026 rows=23 loops=1)"
"                          Output: si.storecode, si.storename, si.storetype, si.gustoreid"
"                          Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"                          ->  Seq Scan on public.inv_storeinformation si  (cost=0.00..1.23 rows=23 width=612) (actual time=0.009..0.013 rows=23 loops=1)"
"                                Output: si.storecode, si.storename, si.storetype, si.gustoreid"
"              ->  Hash  (cost=4.22..4.22 rows=122 width=44) (actual time=0.093..0.093 rows=122 loops=1)"
"                    Output: ppk.guproductpkgid, ppk.code, ppk.description"
"                    Buckets: 1024  Batches: 1  Memory Usage: 10kB"
"                    ->  Seq Scan on public.sms_productpackagemaster ppk  (cost=0.00..4.22 rows=122 width=44) (actual time=0.007..0.038 rows=122 loops=1)"
"                          Output: ppk.guproductpkgid, ppk.code, ppk.description"
"Total runtime: 184242.421 ms"

- Pirinthan

---

explain(analyze, verbose)的输出是什么？您为什么认为成本是个问题？查询运行需要多长时间？ - user330315

вҖңSort Method: external merge Disk: 109184kBвҖқжҳҜдҪ зҡ„дё»иҰҒй—®йўҳгҖӮе°қиҜ•еўһеҠ дҪ зҡ„дјҡиҜқдёӯзҡ„work_mem并йҮҚиҜ•гҖӮиҝҷдәӣиЎЁдёҠжңүе“Әдәӣзҙўеј•еҸҜз”Ёпјҹ - user330315

排序占据了97%的时间：http://explain.depesz.com/s/rU3 - Frank Heikens

在同一会话中使用 psql 执行查询时，“show work_mem” 命令会返回什么？ - David Aldridge

您正在对8列中的100万行进行分组。其中一个名为“描述”的列可能包含较长的字符串值。这将需要占用内存。如果您在一个新问题中解释您尝试解决的潜在问题，可能会有更好的解决方法。 - user330315

Answer 1

我不确定按照SELECT部分中计算的SI.gustoreid进行分组是否有意义... 如果这是你的错误，并且你希望按gustoreid分组进行计数，请考虑以下查询 - 子查询减少了需要分组的列数，可以减少查询的复杂性：

SELECT  
    eco.operationalstatus,
    SI.storecode,
    rowCount,
    SI.storename,
    SI.storetype, 
    PPK.guproductpkgid,
    PPK.code AS ProductCode, 
    PPK.description as ProductName
FROM 
    (
        SELECT
            storecode,
            count(*) AS rowCount,
            gustoreid, 
            storename,
            storetype   
        FROM
            inv_storeinformation 
        GROUP BY
            storecode,
            storename,
            storetype,
            gustoreid
    ) as SI
        LEFT OUTER JOIN sms_ecotransaction as et  on SI.gustoreid=et.currentgustoreid
        LEFT OUTER JOIN sms_ecomaster as eco  on et.refid=eco.ecoid
        LEFT OUTER JOIN sms_productpackagemaster as PPK on eco.guproductid=PPK.guproductpkgid
order by 
    SI.storecode,
    PK.code

- Maciej Nowicki