Very slow Influxql performance (Influxdb 2.6)

## description

We are evaluating a migration from influxdb 1.8 to 2.6 and we noticed that some very simple queries became very slow, and trigger very high cpu load.
This seems a performance regression, and I suspect this is a bug.

The queries that are failing are the type queries of that influxdb is actually designed for. Query for data in a certain (small) time window. Such queries should be very fast.

I reproduced the problem on a small database. Its a database collecting p1 measurements of a device (~ 1 insert second, 1 device).

I query for the latest data. (show all data for the last 5 seconds).
Using influxql I get very bad performance.

Please advise.

## Refiling
I did report this performance issue, on the Influx Community site [here](https://community.influxdata.com/t/very-slow-influxql-performance-influxdb-2-6/29071). But from the replies I derived that that is not the proper place to report such problems. Therefor I refile the same issue here. Let me know this is not the proper location to file such issues.

## Steps to reproduce
influxql query (5s range)
`select * from p1_values where time > '2023-03-15T15:10:25+00:00' and time <= '2023-03-15T15:10:30+00:00';
`
## results
1 queries took 0.124614s (returned 5 rows), (this is a query done on localhost, no network overhead)
Running this periodically, (2 times per second) (over a persistent connection to the database) triggers a significant cpu load on the database server (cpu consumed by influxd process).

## Expected behavior
I’d expect such query to finish in the 1-2 ms range, not > 120ms. On influxdb 1.8 similar queries take < 1 ms on a much larger database. (~200GB)

## database
small database

```
# du -sh /var/lib/influxdb/engine/data/
2.2G /var/lib/influxdb/engine/data/
```
I have a low cardinality dataset.

`influxdb.cardinality(bucket: "energy", start: -1y)`

gives

```
Result: _result
Table: keys: []
_value:int
--------------------------
4
```
```
show tag keys

┏━━━━━━━┳━━━━━━━━┓
┃ index ┃ tagKey ┃
┣━━━━━━━╋━━━━━━━━┫
┃ 1┃region ┃
┃ 2┃sensor ┃
┣━━━━━━━┻━━━━━━━━┫
┃ 2 Columns, 2┃
┃ Rows, Page 1/1┃
┃ Table 1/1,┃
┃ Statement 1/1┃
┗━━━━━━━━━━━━━━━━┛
```
```
show field keys

Name: p1_values
┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ index ┃ fieldKey ┃ fieldType ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━┫
┃ 1┃CURRENT_ELECTRICITY_DELIVERY ┃float ┃
┃ 2┃CURRENT_ELECTRICITY_USAGE ┃float ┃
┃ 3┃DEVICE_TYPE ┃float ┃
┃ 4┃ELECTRICITY_DELIVERED_TARIFF_1 ┃float ┃
┃ 5┃ELECTRICITY_DELIVERED_TARIFF_2 ┃float ┃
┃ 6┃ELECTRICITY_USED_TARIFF_1 ┃float ┃
┃ 7┃ELECTRICITY_USED_TARIFF_2 ┃float ┃
┃ 8┃HOURLY_GAS_METER_READING ┃float ┃
┃ 9┃INSTANTANEOUS_ACTIVE_POWER_L1_NEGATIVE ┃float ┃
┃ 10┃INSTANTANEOUS_ACTIVE_POWER_L1_POSITIVE ┃float ┃
┃ 11┃INSTANTANEOUS_ACTIVE_POWER_L2_NEGATIVE ┃float ┃
┃ 12┃INSTANTANEOUS_ACTIVE_POWER_L2_POSITIVE ┃float ┃
┃ 13┃INSTANTANEOUS_ACTIVE_POWER_L3_NEGATIVE ┃float ┃
┃ 14┃INSTANTANEOUS_ACTIVE_POWER_L3_POSITIVE ┃float ┃
┃ 15┃INSTANTANEOUS_CURRENT_L1 ┃float ┃
┃ 16┃INSTANTANEOUS_CURRENT_L2 ┃float ┃
┃ 17┃INSTANTANEOUS_CURRENT_L3 ┃float ┃
┃ 18┃INSTANTANEOUS_VOLTAGE_L1 ┃float ┃
┃ 19┃INSTANTANEOUS_VOLTAGE_L2 ┃float ┃
┃ 20┃INSTANTANEOUS_VOLTAGE_L3 ┃float ┃
┃ 21┃LONG_POWER_FAILURE_COUNT ┃float ┃
┃ 22┃SHORT_POWER_FAILURE_COUNT ┃float ┃
┃ 23┃VOLTAGE_SAG_L1_COUNT ┃float ┃
┃ 24┃VOLTAGE_SAG_L2_COUNT ┃float ┃
┃ 25┃VOLTAGE_SAG_L3_COUNT ┃float ┃
┃ 26┃VOLTAGE_SWELL_L1_COUNT ┃float ┃
┃ 27┃VOLTAGE_SWELL_L2_COUNT ┃float ┃
┃ 28┃VOLTAGE_SWELL_L3_COUNT ┃float ┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━┫
┃ 3 Columns, 28 Rows, Page 1/1┃
┃ Table 1/1, Statement 1/1┃
```

## build-tsi
I’ve tried rebuilding the indexes using influxd inspect build-tsi. This did not have any (positive nor negative) effect on the performance.

## version
InfluxDB v2.6.1 (git: 9dcf880fe0) build_date: 2022-12-29T15:53:07Z
on Ubuntu 20.04.6 LTS \n \l

Queries executed on localhost using python script using client influxdb==5.3.1 from influxdb import InfluxDBClient

Unfortunately running the query on the CLI (influx v1 shell) does not show how long the query takes. (It shows the result, but not the duration, so I couldn’t test without a python client)
On influxdb 1.8 our python clients perform just fine. (as mentioned above, <1ms query times)

## Performance
Find attached the generated profiles
[iostat.txt](https://github.com/influxdata/influxdb/files/11015344/iostat.txt)
[profiles.tar.gz](https://github.com/influxdata/influxdb/files/11015346/profiles.tar.gz)

Whats further interesting too see, is that the cpu load triggered by this particular query, behaves like a saw tooth in a 24h window.

NB: cpu graph is from a dedicated test system that only runs this particular query periodically (+ 1 insert/sec load) to investigate this problem.

Start by reproducing the InfluxQL five-second query on InfluxDB 2.6.1 using the reported low-cardinality dataset, then review the attached iostat.txt and profiles.tar.gz. Compare latency and CPU use with the stated InfluxDB 1.8 baseline; done means identifying and correcting the server-side regression with a verified performance result.

Written by the indexing model from the issue text.