sql - Suggest a better query execute plan -


am new in coding,so question may noob

i trying executing large select query temp_table

create temp table tmp_tblp  (my select query long, consists of many inner joins)
  • this query running long time (more 6 mins because selected few amount of criteria condition , and date period taken 10days in actual case 1 month) , returns 4443 rows
  • i wanted know there preparation take in server side executing these kind of queries

my database postgresql 9.2

and here query

    select gtab04.productid, gtab04.product, gtab04.saleunit ,gtab04.packing,  gtab04.convfact,gtab04.ptr, gtab04.mrp, gtab04.prate, gtab04.ptr1, gtab04.ptr2,  gtab04.location,0::integer manufid, gtab07.patentid, gtab07.patent, gtab07.patentcd,  gtab15.taxid, gtab15.taxname, gtab15.taxtype, gtab15.taxrate, gtab15.txomrp,''::character  varying(50) manufacture, case when exists (select gtab10.productid gtab10 inner  join gtab09 on gtab09.tranid = gtab10.tranid  gtab10.productid = gtab04.productid ,  gtab10.batchid = gtab05.batchid , gtab09.acyrid = 7 limit 1) 1 else 0 end  stkentered,  (select coalesce(sum(gtab10.qty), 0) gtab10 inner join gtab09 on  gtab10.tranid = gtab09.tranid gtab10.productid = gtab04.productid , gtab10.batchid  = gtab05.batchid , gtab09.vrid = 6 , gtab09.acyrid = 7) + (select  coalesce(sum(gtab10.qty), 0) gtab10 inner join gtab09 on gtab10.tranid = gtab09.tranid   gtab10.productid = gtab04.productid , gtab10.batchid = gtab05.batchid ,  (gtab09.vrid in (10,11,23,42,35)) , gtab09.trdate < cast('2014-05-01' timestamp) ,  gtab09.acyrid = 7) openinginqty,(select coalesce(sum(gtab10.qty),0) gtab10 inner  join gtab09 on gtab10.tranid = gtab09.tranid  gtab04.productid = gtab10.productid ,  gtab10.batchid = gtab05.batchid , (gtab09.vrid in  (12,32,33,44,45,46,47,48,49,18,34,25,27,15,26,24,43,36)) , gtab09.trdate < cast('2014-05- 01' timestamp) , gtab09.acyrid = 7) openingoutqty,0 purchqty,0 sretqty,0  perinqty, 0 saleqty, 0 stockinqty, 0 stockoutqty, 0 saleamt, 0 damageqty, 0  pretqty, 0 peroutqty, (select coalesce(sum(gtab10.qty), 0) gtab10 inner join  gtab09 on gtab10.tranid = gtab09.tranid gtab04.productid = gtab10.productid ,  gtab10.batchid = gtab05.batchid , ((gtab09.vrid in (12,32,33,44,45,46,47,48,49)) ,  (gtab09.trdate between '2014-04-01' , '2014-04-30'))) prmthsaleqty, (select  (coalesce(sum(gtab10.qty * gtab10.ptr), 0)) gtab10 inner join gtab09 on gtab10.tranid  = gtab09.tranid gtab04.productid = gtab10.productid , gtab10.batchid =  gtab05.batchid , (gtab09.vrid in (12,32,33,44,45,46,47,48,49)) , (freeorrpl = 0 or  freeorrpl = 2) , (gtab09.trdate between '2014-04-01' , '2014-04-30')) prmthsaleamt,  gtab04.landcost,gtab05.ptr batchptr,(case when (gtab05.fqty -  gtab05.fiqty)>0  (gtab05.fqty -  gtab05.fiqty) else 0 end) issdfreeqty, gtab05.mrp batchmrp,  gtab05.prate batchprate, gtab04.stdpack,  (date_part('day',  (select min(expiry)  gtab05  gtab05.productid = gtab04.productid , gtab05.qty > gtab05.iqty)-cast('2014- 05-30' timestamp))) expdys, (select  gtab09.trdate gtab09 inner join gtab10 on  gtab09.tranid = gtab10.tranid gtab09.vrid in (12,32,33,44,45,46, 47,48,49) ,  gtab10.productid =gtab04.productid order trdate desc limit 1) lastsaledate  ,gtab05.batchid, 0 freesaleqty, 0 replsaleqty   gtab04 inner join  gtab15 on  gtab04.taxid = gtab15.taxid  left join gtab05 on gtab04.productid = gtab05.productid    inner join gtab07 on gtab07.patentid = gtab04.patentid  (gtab04.masked = false ,  gtab04.banned = false)  , (gtab04.patentid in(  321,313,267,431,307,323,302,425,348,414,378,295,333,358,396,360,393,392,355,363,332,406,365 ,317,387,271,293,384,309,370,347,391,377,288,371,398,326,423,402,334,290,325,337,374,328,40 7,432,345,279,297,280,329,419,289,283,277,292,369,372,367,351,430,411,424,327,400,366,354,3 82,274,427,341,304,389,272,316,300,403,404,380,322,287,381,281,375,276,311,324,284,310,426, 434,376,401,273,364,379,362,413,346,353,395,269,305,344,278,361,301,320)) order patent,  gtab04.product

try 

set temp_buffers = '1gb';  set work_mem = '1gb'; set shared_buffers='1gb'; set effective_cache_size='1gb';

before executing query, above values 3gb ram

  • shared_buffers should 10% 25% of available ram
  • effective_cache_size should 75% of available ram

Comments

Post a Comment

Popular posts from this blog

commonjs - How to write a typescript definition file for a node module that exports a function? -

consider, toml node module can use: // toml.d.ts declare module toml { export function parse(value:string):any; } declare module "toml" { export = toml; } then: /// <reference path="../../../../../defs/toml/toml.d.ts"/> import toml = require('toml'); toml.parse(...); however, node module export single function, such 'glob' ( https://github.com/isaacs/node-glob ). the node usage of module be: var glob = require("glob") glob("*.js", {}, callback(err, files) { ... }); you'd naively expect this: // glob.d.ts declare function globs(paths:string, options:any, callback:{(err:any, files:string[]):void; ...but because typescripts 'import' semantics bit strange, seems can use 'import .. = require()' statement alias modules . trying call: /// <reference path="../../../../../defs/glob/glob.d.ts"/> import blog = require('glob'); results in: error ts2072: mod
Read more

openid - Okta: Failed to get authorization code through API call -

i'm integrating okta own idp server using okta's api. i'm implementing authorization code flow following steps below: in own server, use /api/v1/authn endpoint sessiontoken. use sessiontoken obtain authorization calling endpoint: /oauth2/v1/authorize?client_id=" + clientid + "&sessiontoken=" + sessiontoken + "&response_type=code&response_mode=query&scope=openid&redirect_uri=" + redirecturl + "&state=evanyang&nonce=" it's supposed return response status code 302 , location header containing redirect url code value. however, keep getting response status code 200 , without location header, html body saying "you using unsupported browser." , "javascript disabled on browser." according api documentation: http://developer.okta.com/docs/api/resources/oidc.html#authentication-request , sessiontoken parameter sufficient this: an okta one-time sessiontoken. allows api-based
Read more

thorough guide for profiling racket code -

did googling ("racket profiling", "racket measure performance"), didn't find , there no examples in docs. did google "profile" search on htdp - no luck. (profile (f ...)) output isn't obvious other small snippets. ideally want python's python -m cprofile usage examples. when search "python profiling", both duckduckgo , google give top result: 26.4. python profilers . (although scanned quickly, seems more of reference "thorough usage guide" examples. if had else in mind, perhaps please link that?) the equivalent racket documentation be: profile: statistical profiler . a usage example: #lang racket (require profile) (profile-thunk (thunk (function-to-profile arg0 arg1) )) here (thunk e) convenience (lambda () e) . bigger example: #lang racket (module mod racket (provide f) (define (f) (for/list ([i 10000]) i))) (require (prefix-in mod: 'mod)) (define (f) (for ([i 10000
Read more