influxdata / influxdata/influxdb

Subqueries run much slower for simple SELECT statement

Open
#9,122 21 comments 1 reaction 1 assignee Claimed by @dgnorton View on GitHub
1.x needs-backport/1.7
Dominant language
Rust
Stars
31.7k
Forks
3.7k
Avg merge
13h 37m
Merged PRs (30d)
8

Description

### Bug report

__System info:__ [Include InfluxDB version, operating system name, and other relevant details]

Amazon Linux
InfluxDB 1.3.6

__Steps to reproduce:__

1. Run a query counting and averaging response times:
```
> SELECT mean(response_time),count(response_time) FROM fastly_logs WHERE time > now() - 1m GROUP BY account_id
name: fastly_logs
tags: account_id=3855502814001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.0194 5

name: fastly_logs
tags: account_id=4603804221001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.642 1

name: fastly_logs
tags: account_id=5102072605001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.016 3

name: fastly_logs
tags: account_id=5130362111001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.04241873600155608 10269

name: fastly_logs
tags: account_id=5219833342001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.05869871794871796 156

name: fastly_logs
tags: account_id=5330942432001
time mean count
---- ---- -----
2017-11-16T14:03:13.0021277Z 0.35833333333333334 6

...
...
```
2. Run the same query but within a subquery, selecting some or all elements of the query:
```
> SELECT mean,count FROM (SELECT mean(response_time),count(response_time) FROM fastly_logs WHERE time > now() - 1m GROUP BY account_id)
name: fastly_logs
time mean count
---- ---- -----
2017-11-16T14:03:35.169381598Z 0.015 8
2017-11-16T14:03:35.169381598Z 0.11424999999999999 5
2017-11-16T14:03:35.169381598Z 0.0625 9
2017-11-16T14:03:35.169381598Z 0.0315 8
2017-11-16T14:03:35.169381598Z 0.23199999999999998 6
2017-11-16T14:03:35.169381598Z 2
2017-11-16T14:03:35.169381598Z 0.012 1
2017-11-16T14:03:35.169381598Z 0.042096663600633646 18626
2017-11-16T14:03:35.169381598Z 0.05981645569620251 228
2017-11-16T14:03:35.169381598Z 0.4050000000000001 7
2017-11-16T14:03:35.169381598Z 0.004 8
2017-11-16T14:03:35.169381598Z 0.016 4
2017-11-16T14:03:35.169381598Z 0.3765 3
2017-11-16T14:03:35.169381598Z 0.057 5
```

__Expected behavior:__ [What you expected to happen]

I would expect these queries to take about the same amount of time

__Actual behavior:__ [What actually happened]

the 2nd query takes around 30-60s whereas the first returns almost instantly

__Additional info:__ [Include gist of relevant config, logs, etc.]

I will attach vars.txt and iostat.txt in a comment

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.