postgresql - Performance and index usage for function based queries and function calls inside functions -


i'm trying improve performance of postgresql functions , function calls inside functions.

in our application, postgresql (9.6) queries totally based on functions. example, in order fetch lot of "items" code lines below might used:

create or replace function public.item_read_one(     "itemid" integer)   returns jsonb $body$ declare     outputvariable jsonb; begin   select row_to_json (my_subquery) (    select    id,    "simplefield1",    "simplefield2",    item_status(id,"crucialfield") status    item_table    id = $1 , deleted not true)  my_subquery outputvariable ; return outputvariable; end; $body$   language plpgsql volatile   cost 100; alter function public.item_read_one(integer)   owner some_user;

you see, inside inner select there - beside simple fields - function call. these inner functions plv8 based , contain lot of javascript logic, including database queries. example might such:

create or replace function public.item_status(     "itemid" integer,     "crucialfield" text)   returns jsonb $body$  var returnstatus = {}; var myvariable; if(crucialfield == 'foo') {   myvariable = plv8.execute('select other_table "itemid" = $1',[itemid]); }  else if(crucialfield == 'sweetkitten') {   myvariable = plv8.execute('select "somethingelse" kitten "itemid" = $1',[itemid]); }  /*  lot more javascript logic here. idea, hope. */  return returnstatus;  $body$   language plv8 immutable   cost 100; alter function public.item_status(integer, text)   owner some_user;

aside question, whether design makes sense, problem is: how improve performance?

the data returned functions item_status pretty stable, make sense put index, don't know how.

postgresql version used 9.6.


Comments

Post a Comment

Popular posts from this blog

php - How to add and update images or image url in Volusion using Volusion API -

actually have store importing products store product data insert , update image or image url not insert , update. this xml code name datapro.txt. <products_joined> <productcode>3710_012t</productcode> <vendor_partno>eah5450silent/di/1gd3(lp)</vendor_partno> <productname>test product ta</productname> <hideproduct>n</hideproduct> <stockstatus>20</stockstatus> <lastmodified>1/5/2016 10:25:00 am</lastmodified> <lastmodby>2</lastmodby> <productweight>0.9</productweight> <productprice>100</productprice> <productmanufacturer>asus tek</productmanufacturer> <vendor_price>32.69</vendor_price> <numproductssharingstock>0</numproductssharingstock> <categoryids>107</categoryids> <producturl>http://tebkq.mvlce.servertrust.com/productdetails.asp?productcode=3710_012t</producturl>
Read more

javascript - jQuery UI Splitter/Resizable for unlimited amount of columns -

using jqueryui splitter or resizable functions want create layout allows unlimited amount of columns added , columns need resizable. so fiddle: https://jsfiddle.net/f3glh3mw/ but in fiddle html contains out of nested sets instead of inline. limits possibilities above fiddle can have equal widths 2,4,8,16,32 etc columns. in between shows crooked can see in fiddle. when researching came on question: jquery ui , splitter if @ top answer shows structure like: <div class="wrap"> <div class="resizable resizable1"></div> <div class="resizable resizable2"></div> </div> full fiddle: http://jsfiddle.net/8qztj/86/ which structure want javascript hardcoded 2 resizable items. so here question: how modify last fiddle works amount of .resizable divs. or know of plugin can this. thanks helping! so far found plugin: http://www.bacubacu.com/colresizable/ which says , close functionality want. in
Read more

javascript - IE9 error '$'is not defined -

Image
<!--[if lte ie 9]><html class="lte9"><![endif]--><!--[if gt ie 9]><html><![endif]--> <!--[if !ie]><!--> <html> <!--<![endif]--> blah blah <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <script src="{{ asset('js/mypage.js') }}" type="text/javascript"></script> </body> </html> when load page, ie9 brings error '$' not defined . how can solve error? +++ mypage.js $(function(){ //when load page, //ie9 tag not moving. $(window).load(function() { $('div.mask').hide(); }); // when click , willshow show. //for not ie 9 if (!$('html').hasclass('lte9')) { var = $('ul.about > li > span.activebtn');
Read more