[Feedback] Documented Subqueries outdated / pretty taxing

[erinmikailstaples]

Is it easy to find the information you need?

Yes

Are the instructions clear?

No

How could we improve the Tiger Data documentation site?

From Michal Bartak in the community slack:

I believe I found a bug in the documentation.The example of using time_bucket_gapfill() with interpolate() presents subselects for datapoints from outside of time range (prev and next), where conditions and ordering of these subselects are wrongHope it will help.

these subqueries are pretty taxing. In the past, I saw a github issue for that. The solution I found is to limit the subquery search by additional arbitrary boundary. ie time < X AND time > X - '1d'::INTERVAL . Maybe it should be mentioned in docs.Since the TimescaleDb has introduced last and first values, I expected it's more performant than ORDER BY LIMIT 1 approach.  In this particular case, even with additional arbitrary boundary, it's still a bit slower

[github-actions]

Thank you for the report. We welcome documentation contributions!

• For information about how to propose a change, see the contributing guide in our GitHub repository.

[erinmikailstaples]

Tagging in @michalk-k who first reported this in Slack :)

[michalk-k]

I’ve re-read the OP and realized that the remarks under the image may be confusing, so let me rephrase them:

1. The subqueries proposed in the current documentation are unexpectedly expensive. The solution is to constrain these subqueries by adding boundaries, which limits the scanned range. This is probably worth mentioning, as it reduces execution time by about 50%.

2. One would expect the last() and first() functions introduced by TimescaleDB to be better optimized than using ORDER BY … LIMIT 1. In my case, they are not. They still benefit from the approach described in point 1, but remain slightly slower.