Do you speculate that the 10,000 rows would be randomly spread because of the uuid-type that we chose, namely the uuid-4 type? i.e. the completely random one? If we'd chosen the uuid-1 type (mac address+timestamp), rows would have been more regularly placed and so faster to pull back? Just curious as to why you said the randomly spaced. Also bear in mind that we did experiment with both btree and hash index on the uuid column.
No, I mean that the data corresponding to 10000 UUIDS very likely random distributed over the table, and as a result over HDD.
So getting each single row mean 1 seek on HDD which usually took 5-10ms. You will see the same issue with integer type (or any other type) as well.
Btw, good test for this theory is execute the same query few time in short period of time and see if the second and later runs become faster.
RE: increasing the memory. Currently at 64GB, with following conf settings:
Is it worth (do you think) experimenting with work_mem, and if so to what degree?
work_mem doesn't help there.
If we did add more RAM, would it be the effective_cache_size setting that we would alter?
Yep.
Is there a way to force PG to load a particular table into RAM? If so, is it actually a good idea?
There are no such way except setting the shared buffers equal or bigger than the database size (if you have enough RAM of course).
If the table being accessed quite actively and there are not a lot of memory pressure on the database - than table will be in RAM anyway after some time.
"People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."