On Thu, Feb 28, 2013 at 12:13 PM, Carlo Stonebanks
<stonec.register@sympatico.ca> wrote:
<<Could you use CLUSTER on the table after it had been closed off? If appropriate, that should make the queries run much faster, as elated entries will be in the same or nearby blocks on disk.
>>
Technically, yes. That would really help, but the issue is scheduling. Although the logs are closed off for writes, they aren’t closed off for reads, ref PG documentation: “When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished.”
Not ideal, but a lot better than doing nothing at all!
Since it is read only, you could make a copy of the table, cluster the copy (or just do the sorting while you make the copy), and then atomically swap the two tables by renaming them inside a single transaction.
The swap process will take an exclusive lock, but it will only last for a fraction of second rather than the duration of the clustering operation.
Cheers,
Jeff