Thread: Clustered tables improves perfs ?

hi!
I wonder if clustering a table improves perfs somehow ?
Any example/ideas about that ?
ref : http://www.postgresql.org/docs/8.2/interactive/sql-cluster.html
thx,
P.

On 9/13/07, Patrice Castet <pcastet@agematis.com> wrote:
> I wonder if clustering a table improves perfs somehow ?

As I understand it, clustering will help cases where you are fetching
data in the same sequence as the clustering order, because adjacent
rows will be located in adjacent pages on disk; this is because hard
drives perform superbly with sequential reads, much less so with
random access.

For example, given a table foo (v integer) populated with a sequence
of integers [1, 2, 3, 4, ..., n], where the column v has an index, and
the table is clustered on that index, a query such as "select v from
foo order by v" will read the data sequentially from disk, since the
data will already be in the correct order.

On the other hand, a query such as "select v from foo order by
random()" will not be able to exploit the clustering. In other words,
clustering is only useful insofar as your access patterns follow the
clustering order.

Alexander.

pcastet@agematis.com (Patrice Castet) writes:
> I wonder if clustering a table improves perfs somehow ?
> Any example/ideas about that ?
> ref : http://www.postgresql.org/docs/8.2/interactive/sql-cluster.html

Sometimes.

1.  It compacts the table, which may be of value, particularly if the
table is not seeing heavy UPDATE/DELETE traffic.  VACUUM and VACUUM
FULL do somewhat similar things; if you are using VACUUM frequently
enough, this is not likely to have a material effect.

2.  It transforms the contents of the table into some specified order,
which will improve efficiency for any queries that use that specific
ordering.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://linuxdatabases.info/info/emacs.html
"You can swear at the keyboard  and it won't be offended. It was going
to treat you badly anyway" -- Arthur Norman