JOIN experiment_run_phases ON experiment_run_phases.id = agents.experiment_run_phase_id
JOIN agents ON agents.id = muscle_actions.agent_id Jsonb_array_elements(jsonb_path_query_array(CAST(muscle_nsor_readings AS JSONB), '$.*.value')) AS vm The following query is a prime example for typical queries that run against the database: WITH voltages AS The database stores results from experiments, so analyses usually try to aggregate data before we shovel rows into Jupyter Notebooks to conduct more number crunching, statistical analysis, and plotting using pandas, Dask, etc.
( Update: Adding a composite index over agent_id, id, and walltime as suggested by TimescaleDB does not change the query plan.) Query (I intentionally left out another 17497 skipped seq scans) I.e., executing the following query yields: EXPLAIN ANALYZE SELECT jsonb_array_elements(jsonb_path_query_array(CAST(muscle_nsor_readings AS JSONB), '$.*.value')) AS vm FROM muscle_actions LIMIT 10000 The Hypertable is partitioned by id and walltime. Number of child tables: 17499 (Use \d+ to list them.) Ts_insert_blocker BEFORE INSERT ON muscle_actions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker() "fk_muscle_actions_agent_id_agents" FOREIGN KEY (agent_id) REFERENCES agents(id) "ix_muscle_actions_id" UNIQUE, btree (id) "ix_muscle_actions_agent_id" btree (agent_id) "pk_muscle_actions" PRIMARY KEY, btree (id) Walltime | timestamp without time zone | | not null | Id | integer | | not null | nextval('muscle_actions_id_seq'::regclass) As an example, this is the output of \d muscle_actions, one of the tables that are most prominently queried: Table "public.muscle_actions"Ĭolumn | Type | Collation | Nullable | Default Indexes on tables exist for primary and foreign keys. The following database schema is realized in a PostgreSQL 14.2 database with TimescaleDB 2.5.1:
0 kommentar(er)
