search

timescale / tsbs

Time Series Benchmark Suite, a tool for comparing and evaluating databases for time series data
MIT License
1.26k stars 297 forks source link

Wrong indexes at distributed hypertable for iot case #182

Open dssysolyatin opened 2 years ago

dssysolyatin commented 2 years ago

Hi! There is a point in the documentation for tsdb_load_timescale:

Please note, -in-table-partition-tag is required for multi-node TimescaleDB benchmark testing. Currently, the multi-node query engine does not support push-down of JOINs and so the most efficient queries will filter on the primary tag name.

If specify -in-table-partition-tag flag then index (name, time) will be created instead of (tags_id, time). But benchmark has queries which look like:

SELECT t.%s, t.%s, r.*
FROM tags t INNER JOIN LATERAL
    (SELECT longitude, latitude
    FROM readings r
    WHERE r.tags_id=t.id
    ORDER BY time DESC LIMIT 1)  r ON true
WHERE t.%s`

It is superslow because readings does not have (tags_id, time) index