vishesh92 commented on PR #8737:
URL: https://github.com/apache/cloudstack/pull/8737#issuecomment-1979497377
> @vishesh92 could you share a description of the tests that you've done and
their results?
Sure. I created a table with 661683 entries for timestamp ranging from
`1970-11-29` till `2024-03-07 00:00:00`.
```sql
EXPLAIN ANALYZE SELECT * FROM vm_stats WHERE timestamp < '2000-01-01' ;
```
Query plan with index
```sql
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN
|
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| -> Filter: (vm_stats.`timestamp` < TIMESTAMP'2000-01-01 00:00:00')
(cost=66558 rows=330170) (actual time=0.0104..47.4 rows=282075 loops=1)
|
| -> Covering index range scan on vm_stats using temp_idx over
(timestamp < '2000-01-01 00:00:00') (cost=66558 rows=330170) (actual
time=0.00948..35.4 rows=282075 loops=1) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```
Query plan without index
```sql
+---------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN
|
|---------------------------------------------------------------------------------------------------------------------------------------------|
| -> Filter: (vm_stats.`timestamp` < TIMESTAMP'2000-01-01 00:00:00')
(cost=66500 rows=220091) (actual time=0.0198..87.5 rows=282075 loops=1) |
| -> Table scan on vm_stats (cost=66500 rows=660340) (actual
time=0.0186..64 rows=661683 loops=1) |
+---------------------------------------------------------------------------------------------------------------------------------------------+
```
As you can see the index reduced the query time by ~40%.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
