Shane Ambler wrote:
> Just thought I would share some rough numbers here.
> A bit of an unusual edge case but a big time difference...
>
>
Just following up with this comparison.
I have been in contact with the SQLite creator about this issue and have
found that SQLite's design leans it's default settings toward low memory
usage keeping with it's design goals of having a small footprint.
Even with that design goal, it doesn't lock you into that. You can tell
SQLite to use any memory it needs as well as how much of your db is
cached in RAM. You can also build SQLite to use these options as default.
One drawback with large datasets is that temp_store can get then get
larger than available ram invoking swapping and still negating the
benefits. (though I found this to still be quicker than the original tests)
Having said that - setting SQLite's temp_store to memory allows SQLite
to return SELECT DISTINCT(pincode) FROM codes; in around 15 minutes
compared to PostgreSQL's 9.7 minutes.
It seems that SQLite's sorting algorithm's are better suited for in
memory use than on disk use. This issue really only appears in large
datasets as used in my original timing examples. More specifically when
temp storage exceeds the OS disk cache size.
They are aware of this situation and are working on improvements.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz