current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 5 down vote favorite
1

I wrote the query below this cost is around 1770077 I want to reduce the cost please suggest the best option. 

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


When executed, I get the following actual execution plan cost 

"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" is below

"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"
share|improve this question
1  
What is the output of explain (analyze, verbose)? Why do you think the costs are a problem? How long does the query run? – a_horse_with_no_name Apr 8 at 7:13
5  
"Sort Method: external merge Disk: 109184kB" is your main problem. Try to increase the work_mem for your session and try again. Which indexes are available on the tables? – a_horse_with_no_name Apr 8 at 7:38
2  
The sort takes over 97% of the time: explain.depesz.com/s/rU3 – Frank Heikens Apr 8 at 9:22
2  
From psql, in the same session that you are executing the query from, what does "show work_mem" give? – David Aldridge Apr 8 at 11:58
3  
You are grouping 1 million rows over 8 columns. One of them named "description" which indicates potentially long string values. This is going to require memory. There might be better ways to do this if you explain (in a new question what the underlying problem is that you are trying to solve. – a_horse_with_no_name Apr 8 at 12:52
up vote 0 down vote

I am not sure if grouping by SI.gustoreid that is counted in the SELECT section makes sense... If this is yours mistake and you meant count(*) grouped by gustoreid, please consider the query below - subquery reduces number of columns that have to be grouped and it can reduce complexity of the query:

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.