Re: Database tuning

Antonio Fiol =?ISO-8859-1?Q?Bonn=EDn?= <fiol(at)w3ping(dot)com> writes:
> Better: "Compress" your table so that it holds all continuous IDs.

It's not really necessary to do that. Assuming that you know the
range of IDs, you could do

select ... where id >= (random() * MAX) order by id limit 1;

(where you actually need to do the random() calculation on the client
side so you can send a constant in the query; or else cheat using a
user-defined function that's marked "iscachable". Search for
"iscachable" in the mail list archives to see more about that fine
point.)

This should produce an indexscan plan that starts scanning at
(random() * MAX) and stops as soon as it's got the first tuple.

If you have wide gaps in the ID sequence then the items just after
each such gap will be disproportionately likely to be chosen by this
approach. So it's not perfect. But minor irregularity in the sequence
of IDs can be tolerated this way.

regards, tom lane