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

ios - Change Storyboard View using Seague -

i have following code checks connection server, , depending on want change views if no connection found. nsstring *connect = [nsstring stringwithcontentsofurl:[nsurl urlwithstring:@"http://myserver.com"] encoding:nsutf8stringencoding error:nil]; if (connect == null) { [self performseguewithidentifier:@"network_failure" sender:self]; } else { // other code here } my problem not change views. have added storyboard segue between 2 views identifier of network_failure . not have navigation controller or that, problem? thanks help! no not problem. first of all, in objectivec exists nil object oriented null . must use that. so: if(connect == nil) or better: if(!connect) so due fact nil not same of null , probably code does't enter in if scope . you have been able discover breakpoint. anyway: be sure set trigger segue (line) 2 view controller , , name network_failure (that not best name. should instead view1toview2 ) as, m...
Read more