Tag Info

New answers tagged

1

Like @dezso commented, creating a new table and dropping the old used to be faster in old versions, but not any more with the new implementation in pg 9.1. The most common problem with CLUSTER is that it requires an exclusive lock on the table, which does not go well with concurrent access to it. The solution to this problem is pg_repack, which does not ...


2

If you're running Enterprise edition, you can add and remove indices, including clustered indices, with ONLINE. Since your existing PK isn't being used, you can start by dropping it. Then create your new clustered index, and finally drop your old non-clustered index which is now obsolete. ALTER TABLE MyTable DROP CONSTRAINT PK_MyTbl_SrgKey WITH ...


3

You assumption about adjacency is correct. If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between ...



Top 50 recent answers are included