THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
- 1st. with BTree index on Tweet.create_at only,
- 2nd. with BTree index on User.create_at only,
- 3rd. with both indexes presents, consequently, the intersection is introduced.
The entire result is shared in google sheet
In Memory case:
Each query will run ten times. We record the time by average the last fives.
...
We can see that intersection is the best one under current settings. The total time reduction is from 5% to 70%. If the two indexes are vary a lot in the selectivity, then the benefit of intersection may not that much. If the two indexes are of the samilar similar selectivity the intersection can achieve 60% ~ 70% total time reduction.
...
The test dataset is changing to the 8.2G dataset. In order to flush the cache, we load the same dataset to another ds_copy dataset. Every time when we run the selection, we scan this 8.2G ds_copy once to invalidate the cache pages. Due to the slowness of the on disk case, we warm up the query only once and record the average time of the next three times.
Table 4. Fix User.create_at condition for one month and increase the Tweet.creat_at range.
| Scan | user time Index | tweet time index | intersection | overhead | |||
| result | month | hour | Time (Avg last 5) | ||||
| 1166 | 01--02 | 00--01 | 113423 | 112469 | 1397 | 1615 | 15.60% |
| 1953 | 01--02 | 00--02 | 111843 | 1628 | 1915 | 17.63% | |
| 2438 | 01--02 | 00--03 | 112307 | 1802 | 2143 | 18.92% | |
| 2809 | 01--02 | 00--04 | 110893 | 2105 | 2453 | 16.53% | |
| 3347 | 01--02 | 00--05 | 119531 | 3184 | 2503 | -21.39% | |
| 4381 | 01--02 | 00--06 | 110912 | 2552 | 3060 | 19.91% | |
| 5802 | 01--02 | 00--07 | 111488 | 3126 | 3935 | 25.88% | |
| 7594 | 01--02 | 00--08 | 111484 | 4148 | 4992 | 20.35% | |
| 9846 | 01--02 | 00--09 | 111499 | 5299 | 6243 | 17.81% |
Table 5.Fix Tweet.create_at condition for one hour and increase the User.creat_at range.
| Scan | user time Index | tweet time index | intersection | overhead | |||
| result | month | hour | Time (Avg last 5) | ||||
| 1166 | 01--02 | 00--01 | 113423 | 112469 | 1397 | 1615 | 15.60% |
| 1751 | 01--03 | 00--01 | 110207 | 1375 | 1808 | 31.49% | |
| 2262 | 01--04 | 00--01 | 110965 | 1462 | 1738 | 18.88% | |
| 2850 | 01--05 | 00--01 | 112291 | 1371 | 1835 | 33.84% | |
| 3753 | 01--06 | 00--01 | 111587 | 1289 | 1890 | 46.63% | |
| 4679 | 01--07 | 00--01 | 111769 | 1320 | 2029 | 53.71% | |
| 5544 | 01--08 | 00--01 | 112209 | 1340 | 2076 | 54.93% | |
| 6250 | 01--09 | 00--01 | 113029 | 1370 | 2270 | 65.69% | |
| 6955 | 01--10 | 00--01 | 112535 | 1310 | 2329 | 77.79% |
Though the two access methods have very different execution time, the intersection tend to catch with the fastest one. The overhead comparing to the fastest path is from 15% to 78%, while the speed up comparing to the slowest one is about 5~10 times faster.
Review Patch:
https://asterix-gerrit.ics.uci.edu/#/c/577 and https://asterix-gerrit.ics.uci.edu/#/c/578
...