TSInfo function

The TSInfo function returns information about a time series and the storage that is used by the time series.

Syntax

TSInfo(ts           TimeSeries, 
       control      BSON default NULL,
       begin_tstamp DATETIME YEAR TO FRACTION(5) default NULL,
       end_tstamp   DATETIME YEAR TO FRACTION(5) default NULL)
returns BSON;


Syntax of control

>>-{------------------------------------------------------------>

   .-,-----------------------------------------------------------------------.   
   V                                                                         |   
>----+-smallElementPage--:--+-Boolean----------------------+---------------+-+-->
     |                      '-| smallElementPage Options |-'               |     
     +-largeElementPage--:--+-Boolean----------------------+---------------+     
     |                      '-| largeElementPage Options |-'               |     
     +-element--:--+-Boolean-------------+---------------------------------+     
     |             '-| element Options |-'                                 |     
     +-containerPageSize--:--Boolean---------------------------------------+     
     +-btreeLeafSlotSize--:--Boolean---------------------------------------+     
     +-baseTableSize--:--Boolean-------------------------------------------+     
     +-totalIntervalCount--:--Boolean--------------------------------------+     
     +-intervalCount--:--+-Boolean-------------------+---------------------+     
     |                   '-| intervalCount Options |-'                     |     
     +-metadata--:--+-Boolean--------------+-------------------------------+     
     |              '-| metadata Options |-'                               |     
     +-totalPages--:--Boolean----------------------------------------------+     
     +-totalUserSlots--:--Boolean------------------------------------------+     
     +-totalSlots--:--Boolean----------------------------------------------+     
     +-totalUserSize--:--Boolean-------------------------------------------+     
     +-totalSize--:--Boolean-----------------------------------------------+     
     +-totalFree--:--Boolean-----------------------------------------------+     
     +-pageSizeDistribution--:--Boolean------------------------------------+     
     +-memoryUsage--:--+-Boolean-----------------+-------------------------+     
     |                 '-| memoryUsage Options |-'                         |     
     +-compressionBuffer--:--+-Boolean-----------------------+-------------+     
     |                       '-| compressionBuffer Options |-'             |     
     +-compressionSessionStats--:--+-Boolean-----------------------------+-+     
     |                             '-| compressionSessionStats Options |-' |     
     '-rollingWindows--:--+-Boolean--------------------+-------------------'     
                          '-| rollingWindows Options |-'                         

>--}-----------------------------------------------------------><

ts

The name of the TimeSeries column.

control

A BSON document that describes which values to return and the corresponding structure of the output document. Each field can have a Boolean value of true or false. Some fields can have subdocuments that refine the return value. Output values are in integers, unless otherwise indicated. The default document for the control parameter sets all values to true except for dormant rolling window containers:

{
        smallElementPage:true,
        largeElementPage:true,
        element:true,
        containerPageSize:true,
        btreeLeafSlotSize:true,
        baseTableSize:true,
        totalIntervalCount:true,
        intervalCount:true,
        metadata:true,
        totalPages:true,
        totalUserSlots:true,
        totalSlots:true,
        totalUserSize:true,
        totalSize:true,
        totalFree:true,
        pageSizeDistribution:true,
        memoryUsage:true,
        compressionBuffer:true
        compressionSessionStats:true
        rollingWindows:{active:true,dormant:false}
        
}

smallElementPage

Information about pages that contain elements that are small enough to fit on one page.


smallElementPage Options

      .-,------------------------------------------------------.      
      V                                                        |      
|--{----+-count--:--Boolean----------------------------------+-+--}--|
        +-size--:--Boolean-----------------------------------+        
        +-free--:--Boolean-----------------------------------+        
        +-distribution--:--Boolean---------------------------+        
        +-+-slot--:---+--+-Boolean-------------------------+-+        
        | '-header--:-'  |    .-,---------------------.    | |        
        |                |    V                       |    | |        
        |                '-{----+-count--:--Boolean-+-+--}-' |        
        |                       '-size--:--Boolean--'        |        
        '-nullPage--:--+-Boolean-----------------+-----------'        
                       '-{--count--:--Boolean--}-'

The count field shows the total number of pages that contain small elements.

The size field shows the total number of bytes on small element pages.

The free field shows the total number of bytes of free space on all small element pages.

The distribution field shows the fullness of small element pages. See Distributions.

The slot field shows two fields. The count field shows the total number of small element slots in the time series. The size field shows the total number of bytes in small element slots, except header slots and large element page directories.

The header field shows two fields. The count field shows the total number of header slots for small element pages. The size field shows the total number of bytes in small element header slots.

The nullPage field shows the total number of pages that have null values for all element slots.

largeElementPage

Information about pages that contain elements that are too large to fit on one page.


largeElementPage Options

      .-,---------------------------------------------------------.      
      V                                                           |      
|--{----+-count--:--Boolean-------------------------------------+-+--}--|
        +-size--:--Boolean--------------------------------------+        
        +-free--:--Boolean--------------------------------------+        
        +-distribution--:--Boolean------------------------------+        
        '-+-slot--:------+--+-Boolean-------------------------+-'        
          +-header--:----+  |    .-,---------------------.    |          
          '-directory--:-'  |    V                       |    |          
                            '-{----+-count--:--Boolean-+-+--}-'          
                                   '-size--:--Boolean--'

The count field shows the total number of pages that contain large elements.

The size field shows the total number of bytes on large element pages.

The free field shows the total number of bytes of free space on all large element pages.

The distribution field shows the fullness of large element pages. See Distributions.

The slot field shows two fields. The count field shows the total number of large element slots in the time series. The size field shows the total number of bytes in large element slots.

The header field shows two fields. The count field shows the total number of header slots for large element pages. The size field shows the total number of bytes in large element header slots.

The directory field shows two fields. The count field shows the total number of directory slots for large element pages. The size field shows the total number of bytes in large element directory slot.

element

Information about the number of physical elements.


element Options

      .-,----------------------------.      
      V                              |      
|--{----+-count--:--Boolean--------+-+--}-----------------------|
        '-notNullCount--:--Boolean-'

The count field shows the total number of physical elements, including null slots.

The notNullCount field shows the total number of non-null physical elements.

containerPageSize

The page size, in bytes, of containers. For example, 2048 or 4096.

btreeLeafSlotSize

The size, in bytes, of B-tree leaf slots that are used by the index on time series data.

baseTableSize

The size, in bytes, of the base table that contains the time series. Includes the size of any in-row time series data.

totalIntervalCount

The total number of partitions in rolling window containers. This value is not affected by the begin_stamp and end_stamp parameters or the setting of the rollingWindows field.

intervalCount

The number of active and dormant partitions in rolling window containers.


intervalCount Options

      .-,-----------------------.      
      V                         |      
|--{----+-active--:--Boolean--+-+--}----------------------------|
        '-dormant--:--Boolean-'

The active field shows the number of active partitions in rolling window containers.

The dormant field shows the number of dormant partitions in rolling window containers.

metadata

Information about the properties of the time series.


metadata Options

      .-,------------------------------.      
      V                                |      
|--{----+-origin--:--Boolean---------+-+--}---------------------|
        +-calendar--:--Boolean-------+        
        +-container--:--Boolean------+        
        +-threshold--:--Boolean------+        
        +-tsid--:--Boolean-----------+        
        +-irregular--:--Boolean------+        
        +-inmem--:--Boolean----------+        
        +-bigtsid--:--Boolean--------+        
        +-rollingWindow--:--Boolean--+        
        +-hertz--:--Boolean----------+        
        +-compression--:--Boolean----+        
        +-originalOrigin--:--Boolean-+        
        '-refCount--:--Boolean-------'

The origin field shows the origin date of the time series. The output value is a string representation of a DATETIME data type in the following format: %Y-%m-%d %H:%M:%S.%F5. For example: 2014-01-01 00:00:00.00000.

The calendar field shows the name of the calendar for the time series as a string value.

The container field shows the name of the container for the time series as a string value. No value indicates that the time series is not stored in a container.

The threshold field shows the threshold value of the time series.

The tsid field shows the time series instance ID as a BIGINT value if the time series elements are stored in containers.

The irregular field indicates whether the time series is irregular (true) or regular (false).

The inmem field indicates whether the time series data is in-row (true) or in containers (false).

The bigtsid field indicates whether the time series instance ID is a BIGINT value (true) or an INTEGER value (false).

The rollingWindow field indicates whether the time series data is stored in rolling window containers (true) or not (false).

The hertz field shows the number of records of hertz data per second that are stored. The value 0 indicates that the time series does not store hertz data.

The compression field shows the compression definition as a string value for compressed time series data. No value indicates that the time series is not compressed.

The originalOrigin field shows the original origin for a regular time series if the origin was reset by the SetOrigin function; otherwise, this value is the same as the value of the origin field. The output value is a string representation of a DATETIME data type in the following format: %Y-%m-%d %H:%M:%S.%F5. For example: 2014-01-01 00:00:00.00000.

The refCount field shows the number of different references to the same time series instance. The value of 1 indicates that the time series data that is in containers is referenced by only one time series row in a table. If you copy the time series row into another table, the value of the refCount field is 2.

totalPages

The total number of element pages that are used by the time series. Equivalent to the number of small element pages plus the number of large element pages.

totalUserSlots

The total number of user slots that are used by the time series. Equivalent to the number of small element page slots plus the number of large element page directory slots.

totalSlots

The total number of slots that are used by the time series. Equivalent to the sum of the number of small element page and header slots and large element page, directory, and header slots.

totalUserSize

The total size, in bytes, of the user pages that are used by the time series. Equivalent to the sum of the size of the small element page slots and the large element page slots.

totalSize

The total size, in bytes, of the pages that are used by the time series. Equivalent to the sum of the size of the small element pages and the large element pages.

totalFree

The total size, in bytes, of free space on both small and large element pages.

pageSizeDistribution

The distribution of the fullness of element pages. Equivalent to the sum of the small and large element page distributions. See Distributions.

memoryUsage

The amount of shared memory, in bytes, that contains the results of time series functions.

compressionBuffer

Information on the compression buffer size and related parameters.


compressionBuffer Options

      .-,------------------------.      
      V                          |      
|--{----+-size--:--Boolean-----+-+--}---------------------------|
        +-rowsize--:--Boolean--+        
        +-pagesize--:--Boolean-+        
        +-nrows--:--Boolean----+        
        '-npages--:--Boolean---'

The size field shows the buffer size in bytes.

The rowsize field shows maximum row size in bytes.

The pagesize field shows the useable page size of the container.

The nrows field shows number of rows based on maximum row size.

The npages field shows the number of pages based on the maximum page size.

compressionSessionStats

Information on the compression stats used in the session. These stats are accumulative during the session and persist until the end of the session.


compressionSessionStats Options

      .-,------------------------------------------.   
      V                                            |   
|--{----+-estimatedUncompressedBytes--:--Boolean-+-+------------|
        '-compressedBytesUsed--:--Boolean--------'

The estimatedUncompressedBytes field shows the total estimated storage bytes used if not compressed.

The compressedBytesUsed field shows the total used portion of the compression buffer.

rollingWindows

Controls whether storage information is returned for rolling window containers or standard containers. If you set the rollingWindows field to false, storage information is returned for standard containers. If you set the rollingWindows field to true, storage information is returned for both active and dormant windows in rolling window containers, but not for standard containers. The default is to return information about only the active windows in rolling window containers. The output for the rollingWindows field shows the input value as a string.


rollingWindows Options

      .-,-----------------------.      
      V                         |      
|--{----+-active--:--Boolean--+-+--}----------------------------|
        '-dormant--:--Boolean-'

The active field controls whether information is returned about active windows in rolling window containers.

The dormant field controls whether information is returned about dormant windows in rolling window containers.

begin_tstamp

The beginning point of the range. Can be NULL, which represents the first element in the time series. If a time stamp is specified, the output value is a string representation of a DATETIME data type in the following format: %Y-%m-%d %H:%M:%S.%F5. For example: 2014-01-01 00:00:00.00000.

end_tstamp

The end point of the range. Can be NULL, which represents the last element in the time series. If a time stamp is specified, the output value is a string representation of a DATETIME data type in the following format: %Y-%m-%d %H:%M:%S.%F5. For example: 2014-01-01 00:00:00.00000.

Distributions

Distributions are an array of 10 positions of integer values that each represent a 10% range of space usage. The value that is returned in each position of the array is the number of pages that are the corresponding percentage full. The following table shows the array positions and the corresponding percentage ranges.

Table 1. Distribution percentage ranges
Array	0	1	2	3	4	5	6	7	8	9
%	0 - 10%	11 - 20%	21 - 30%	31 - 40%	41 - 50%	51 - 60%	61 - 70%	71 - 80%	81 - 90%	91 - 100%

For example, if a time series used 53 small element pages in a container and 48 of the pages are between 95% and 100% full, then the value in the ninth array position is 48. The other five pages are represented in other array positions:

smallElementPage:{distribution:[ 1, 0, 0, 3, 0, 1, 0, 0, 0, 48]}

One page is 0-10% full, three pages are 31-40% full, one page is 51-60% full, and 48 pages are 91-100% full.

Usage

Run the TSInfo function to return information about a time series. By default, the TSInfo function returns detailed information about the storage that is used by the time series and the properties of the time series. Limit the information that is returned by the TSInfo function by specifying the fields that you want in the control parameter.

Include time stamps for the begin_stamp and end_stamp parameters to return information about the time series elements in a specific time range.

The TSInfo function can be CPU intensive, depending on the number of elements for which to gather information and the type of information to return. For example, returning the number of hertz or compressed elements is the most CPU-intensive operation.

Use the following rules for constructing the control parameter:

If any fields that are listed have a value of true, all fields that are not listed are omitted. If you want only specific fields, list only those fields, with the value true. Any subdocuments for the fields that you list are automatically included. Fields that are not listed are not included. For example, the value {smallElementPage:true} returns all the values for smallElementPage and no other information.
If you want to return only specific fields but exclude specific fields in subdocuments, list the subdocuments with a value of false and at least one other field with a value of true. For example, the value {smallElementPage:{count:true, size:false}} returns all the values for smallElementPage except for the value of the size field.
If all fields that are listed have a value of false, all fields that are not listed are included. If you want to return all fields except for specific fields, list the fields to omit with a value of false and do not list any fields with a value of true. For example, the value {smallElementPage:false, largeElementPage:false} returns the values for all fields except smallElementPage and largeElementPage.

Important: Misspelled field names in the control parameter are ignored without an error. If you do not receive the results that you expect, check the spelling of the field names in the control parameter.

Returns

A BSON document with the requested field-name value pairs.

Example: Default information

The following statement returns the default information about a time series instance:

SELECT meter_id, tsinfo(readings)::json::lvarchar AS info
          FROM sensor_data
          WHERE meter_id = 2011;


meter_id        2011
info            {"smallElementPage":{"count":2046,"size":8380416,"free":174012,
"distribution":[0,0,0,0,1,0,0,0,0,2045],"slot":{"count":45000,"size":7875000},
"header":{"count":2046,"size":85932},"nullPage":{"count":0}},"largeElementPage":
{"count":0,"size":0,"free":0,"distribution":[0,0,0,0,0,0,0,0,0,0],"slot":{
"count":0,"size":0},"header":{"count":0,"size":0},"directory":{"count":0,"size":
0}},"element":{"count":45000,"notNullCount":45000},"containerPageSize":4096,
"btreeLeafSlotSize":19,"baseTableSize":92,"totalIntervalCount":0,"intervalCount":
{"active":0,"dormant":0},"metadata":{"origin":"2014-01-01 00:00:00.00000",
"calendar":"ts_1sec","container":"container4k_hz_3","threshold":0,"tsid":41,
"irregular":true,"inmem":false,"bigtsid":true,"rollingWindow":false,"hertz":0,
"compression":"","originalOrigin":"2014-01-01 00:00:00.00000"},"totalPages":2046,
"totalUserSlots":45000,"totalSlots":47046,"totalUserSize":7875000,"totalSize":
7960932,"totalFree":174012,"pageSizeDistribution":[0,0,0,0,1,0,0,0,0,2045],
"memoryUsage":{"count":0,"size":0},"rollingWindows":{"active":true}}

Example: Small element page count for a time range

The following statement returns the small element page count for two time series instances over an hour:

SELECT meter_id,info::json::lvarchar AS info, bson_value_bigint(info, 
           'smallElementPage.count') AS count 
     FROM(SELECT skip 0 meter_id, tsinfo(readings, 
          '{smallElementPage:{count:true}}'::json::bson,
          '2014-01-01 00:00:00.00000',
          '2014-01-01 01:00:00.00000')
          FROM ts WHERE meter_id <= 2) AS ti(meter_id, info);

meter_id        1
info            {"smallElementPage":{"count":0},"rollingWindows":{"active":true},
                "begin_tstamp":"2014-01-01 00:00:00.00000",
                "end_tstamp":"2014-01-01 01:00:00.00000"}
count           0

meter_id        2
info            {"smallElementPage":{"count":0},"rollingWindows":{"active":true},
                "begin_tstamp":"2014-01-01 00:00:00.00000",
                "end_tstamp":"2014-01-01 01:00:00.00000"}
count           0

Example: Total number of pages

The following statement returns the total number of pages for two time series instances:

SELECT meter_id, info::json::lvarchar AS info, bson_value_bigint(info, 
         'totalPages') AS totalpages 
     FROM (SELECT skip 0 meter_id, tsinfo(readings, 
         '{totalPages:true}'::json::bson) 
         FROM devices WHERE  meter_id <= 2) 
     AS ti(meter_id, info);

meter_id        1
info            {"totalPages":52,"rollingWindows":{"active":true}}
totalpages      52

meter_id        2
info            {"totalPages":32,"rollingWindows":{"active":true}}
totalpages      32

Example: Metadata

The following statement returns the metadata information for two time series instances:

SELECT meter_id,info::json::lvarchar FROM
        (SELECT skip 0 meter_id, tsinfo(readings, '{metadata:true}'::json::bson) 
         FROM devices WHERE  meter_id <= 2) AS ti(meter_id, info);

meter_id        1
info            {"metadata":{"origin":"2012-06-01 00:00:00.00000","calendar":
"ts_1min","container":"container4k_1","threshold":0,"tsid":17,"irregular":true,
"inmem":false,"bigtsid":true,"rollingWindow":true,"hertz":0,"compression":"",
"originalOrigin":"2012-06-01 00:00:00.00000","refCount":1},
"rollingWindows":{"active":true}}

meter_id        2
info            {"metadata":{"origin":"2012-06-01 00:00:00.00000","calendar":
"ts_1min","container":"container4k_2","threshold":0,"tsid":18,"irregular":false,
"inmem":false,"bigtsid":true,"rollingWindow":true,"hertz":0,"compression":"",
"originalOrigin":"2014-01-01 00:00:00.00000","refCount":1},
"rollingWindows":{"active":true}}