zsh-histdb: Do we really care a lot about duplicate commands in `histdb` call? Improves histdb perf by 100x
(semi-related to https://github.com/larkery/zsh-histdb/issues/2)
When you remove the group by command and add an index on time:
create index hist_time on history(start_time);
For example compare this (slow):
-- explain query plan
select session, dir, replace(commands.argv, '
', '\n') as argv, max(start_time) as max_start
from
history
left join commands on history.command_id = commands.id
left join places on history.place_id = places.id
where not (commands.argv like 'histdb%') and places.host='phirearch'
group by history.command_id, history.place_id
order by max_start desc
limit 62
to this:
-- explain query plan
select session, dir, replace(commands.argv, '
', '\n') as argv, start_time
from
history
left join commands on history.command_id = commands.id
left join places on history.place_id = places.id
where not (commands.argv like 'histdb%') and places.host='phirearch'
order by history.start_time desc
limit 62
For me, this reduces the duration of simple calls like histdb or histdb borg from 1100ms to 10ms.
It would even be possible to wrap this in another call to remove duplicates with the only caveat that then you may get somewhat less results than you requested with --limit
About this issue
- Original URL
- State: open
- Created 5 years ago
- Comments: 16 (15 by maintainers)
I have a feeling the query plan for this is a bit stupid anyway - it should be scanning commands for matching history rows and then using the index on command_id to efficiently search history, rather than scanning history. I will see whether I can coerce it into doing something else with the same output by moving some joins around.