Hi Roland,
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:
max_connections = 100
shared_buffers = 10GB
effective_cache_size = 45GB
work_mem = 100MB
maintenance_work_mem = 1GB
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB
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.
--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
МойКруг: http://mboguk.moikrug.ru/
"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."
Senior Postgresql DBA
http://www.postgresql-consulting.ru/
Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678
LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
МойКруг: http://mboguk.moikrug.ru/
"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."
