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: 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

    php - How can I cram 6+31 numeric characters into 22 alphanumeric characters? -

    -
    I have a 6 digit number and a 31-digit number (such as "234536" and "201103231043330478311223582826") that I The API needs to be crawled in the same 22-character alphanumeric field that uses PHP. I tried to convert each one to 32 (using custom code could not handle the base_convert () large number) and to connect with a single-character delimiter, but It only gives me 26 characters This is a restore API, so the letters should be URI-protected. I would like to do a database table cross with two references without reference to another reference value, if possible, any suggestions? Instead use a radix of 62. This will give you 22 characters for the top, 3.35 for the pre and 17.3 for the next. & gt; & Gt; & Gt; Math.log (10 ** 6) /math.log (62) 3.3474826039165504 & gt; & Gt; & Gt; Math.log (10 ** 31) /math.log (62) 17.295326786902177
    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