Re: Are bitmap index scans slow to start?

On Thu, Feb 28, 2013 at 12:13 PM, Carlo Stonebanks <
stonec(dot)register(at)sympatico(dot)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