...
| Code Block | ||
|---|---|---|
| ||
cpstemporaldb=# \d network_data
Table "public.network_data"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
timestamp | timestamp with time zone | | not null |
dataspace | character varying(255) | | not null |
schema_setanchor | character varying(255) | | not null |
anchor schema_set | character varying(255) | | not null |
payload | jsonb | | not null |
version | timestamp with time zone | | not null |
Indexes:
"network_data_dataspace_schema_set_anchor_timestamp_idx" UNIQUE, btree (dataspace, schema_set, anchor, "timestamp" DESC)
"network_data_dataspace_anchor_timestamp_idx" btree (dataspace, anchor, "timestamp" DESC)
"network_data_dataspace_schema_set_timestamp_idx" btree (dataspace, schema_set, "timestamp" DESC)
"network_data_payload_idx" gin (payload)
"network_data_timestamp_idx" btree ("timestamp" DESC)
cpstemporaldb=# SELECT * FROM timescaledb_information.dimensions;
hypertable_schema | hypertable_name | dimension_number | column_name | column_type | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+--------------------------+----------------+---------------+------------------+------------------+----------------
public | network_data | 1 | timestamp | timestamp with time zone | Time | 1 day | | |
(1 row)
|
Indexes above are designed to optimize following queries:
...
- By dataspace, anchor. Order by timestamp desc.
- By dataspace, schema set. Order by timestamp desc.
- Adding a payload content condition to the 3 2 kinds of queries above. In this case, the gin index is used in combination with the index having the corresponding fields. See attached explain plan for an example.
The index for (timestamp) is automatically created by Timescale to support the main data partitioning by time.
References
...