sql - JSONB performance degrades as number of keys increase -

-

I am testing the performance of jsonb datatype in postgresql. Each document will have around 1500 keys which are not ordered. Here's how the document is flattened and what the document looks like. Make a table ztable0 (ID serial primary key, data jsonb)

Here is a sample document: 

 / p> 
  {"0": 301, "90": 23, "61": 4001, "11": 9 2 9} ...   
 You can see that the document does not have hierarchies and all values ​​are integers  
  •  Columns: 2  
  •  The key in the document: 1500 +   
  •   
     Search for a particular value of a key When performing a set of time or performance, it is very carefully slowed down. This query:  & gt; & Gt; '1') Select (data -> gt; & gt; '1') :: Integer, by ztable0 (data -> gt; & gt; '1') from calculation (*) :: Integer limit 100 Does it take about 2 seconds to complete   
     Is there any way to improve the performance of jsonb documents?   
     
     
     This is a known issue in  9.4beta2 , please refer to it, This includes some locations and pointers in mail threads.  
     About the problem.  
     PostgreSQL is to store the data value, which means that the larger value (typically 2kB and more rounds) is stored in a particular type of table and postGraceQL is also able to compress the data. Attempts, it is used in the  pglz  method (for ages). By a ???? tries This means that 1k bytes are examined before deciding to include the data. And if the results are not satisfactory, either do not give any benefit to narrowed data, the decision is not compressed.  
     Therefore, in the initial JSNB format the offset table was stored at the beginning of its price. And in JSON it was captured by 1KB (and more) offsets for the higher number of routes in the root. It was a series of specific data, that is, it was not possible to get two adjacent 4-byte sequences that are equal. There is no compression in this way.  
     Note, if an offset passes through the table, the rest of the value will be completely compressed. Therefore, one of the options would be to introduce the  pglz  code explicitly to weather compression and for that (especially for the newly introduced data types), but the existing infrastructure of this Does not support.  
     fixed  
     Then it was decided to change the way the data was stored inside the JSONB value, for more of this code  pglz  Has been made suitable. Here is a new JSONB implementing change on-disk format. And despite the format change, there is still O (1) to detect a random element.  
     Although it took about a month to decide. As I can see,  was 9.4beta3 , so you will be able to re-examine it soon after the official announcement.  
      Important note:  To switch you to  9.4beta3  
     
    > To do or use it, the  pg_upgrade  tool, as you have set up for this issue, has identified the necessary changes in the way data is stored, so the  Beta 3   beta2 .

    • Comments

    Post a Comment

    Popular posts from this blog

    php - PDO bindParam() fatal error -

    -
    I am trying to learn PDO, now I have written this small code, but it gives me a serious error: < P> Fatal error: Call a member function at a non-object dot () ... $ con = new mysqli ("127.0.0.1", "root", "", "csvdangercheck"); $ Query = $ con- & gt; ("Id,` var1`, `var2`) values ​​(" id,: var1,: var2) ";); $ query- & gt; bypass pattern (': id', $ id); $ query- & gt; BindParam (': var1', $ VAL1); $ Query- & gt; bindParam (': var2', $ val2); $ Query-> Executed (); I Trying to use print_r ($ con-> errorInfo ()) but Fatal error: Undefined method mysqli :: errorInfo ( ) ... can someone tell me what am I missing? Flaffeh said, you have PDO Missing Mysqy, try it: $ con = new PDO ('mysql: host = 127.0.0.1; dbname = csvdangercheck', 'root', '');
    Read more

    logging - How can I log both the Request.InputStream and Response.OutputStream traffic in my ASP.NET MVC3 Application for specific Actions? -

    -
    For a specific set of tasks, I need to log in to the incoming request inputstream as well as outgoing response. is. I am using an ActionFilterAttribute for this and overriding the OnActionExecuted and OnResultExecuted methods. So I'm starting with this idea ... Public category ActionLoggerAttribute: ActionFilterArrbit {Public Override Zero On-Offed Execution (ActionActioned Contact Filter Filter) {base.OnActionAxecuted (FilterContext); HttpRequestBase request = filterContext.HttpContext.Request; // TODO: Log the request. InputStream} Public Override Zero OnResultExecuted (ResultExecutedContext FilterContext) {base.OnResultExecuted (filterContext); HTTPRPSPointBase Response = FilterContacts Hpptex response; // TODO: Log on Response.OutputStream}} Ideally I'll hook it up with enterprise library logging because I'm already using it for error logging. Am I reaching the input and output stream at the right time? Can I easily use the Enterprise Library to log i
    Read more

    java - Why my included JSP file won't get processed correctly? -

    -
    I am trying to create Java web framework (and learning), and on the basis of 'Code Generator In the process of making the material view of the database in the process of development, I stumbled into a difficulty, which I do not know how to solve it. First of all, I want the following index.jsp : & lt; Body & gt; Use all pages to be created. & Lt;% @ include file = "header.jsp"% & gt; & Lt; Hours / & gt; & Lt;% @ included file = "body.jsp"%> & Lt; Hours / & gt; & Lt;% @ include file = "footer.jsp"% & gt; & Lt; / Body & gt; And, in body.jsp , I should have it like this: & lt; Jsp: include page = "$ {Application_modul}"%> Where application_modul is an attribute defined in its controller: request.setAttribute ("application_modul", "USER_ACCOUNT \\ ' View_user_account.jsp "); This file can be found properly, but the processed JSP does
    Read more