...
| 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_set | character varying(255) | | not null |
anchor | 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, schema set, anchor. Order by timestamp desc.
- By dataspace, anchor. Order by timestamp desc.
- By dataspace, schema set, payload content. Order by timestamp desc.
For 3. both indexes "network_data_dataspace_schema_set_timestamp_idx" and "network_data_payload_idx" are combined to handle the query.
References
...