JoaoJandre commented on PR #8737:
URL: https://github.com/apache/cloudstack/pull/8737#issuecomment-1980809847
> > @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%.
@vishesh92, from the description of your PR:
```
This PR adds the upgrade path for 4.19 to 4.19.1 and creates two missing
indexes on vm_stats table
...
2. vm_stats -> timestamp - To speed up clean up of old vm_stats.
```
You're proposing the addition of an index on the `timestamp` column to speed
up the deletion of old records on `vm_stats`. Why would you test a **SELECT**
and not a **DELETE**? Could you share the tests results using a **DELETE**
query?
--
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: commits-unsubscr...@cloudstack.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
