pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedClustered tables improves perfs ?
3+ messages / 3 participants
[nested] [flat]
* Clustered tables improves perfs ?
@ 2007-09-13 13:03 Patrice Castet <pcastet@agematis.com>
0 siblings, 2 replies; 3+ messages in thread
From: Patrice Castet @ 2007-09-13 13:03 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clustered tables improves perfs ?
@ 2007-09-13 13:31 Alexander Staubo <alex@purefiction.net>
parent: Patrice Castet <pcastet@agematis.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alexander Staubo @ 2007-09-13 13:31 UTC (permalink / raw)
To: Patrice Castet <pcastet@agematis.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Clustered tables improves perfs ?
@ 2007-09-13 14:49 Chris Browne <cbbrowne@acm.org>
parent: Patrice Castet <pcastet@agematis.com>
1 sibling, 0 replies; 3+ messages in thread
From: Chris Browne @ 2007-09-13 14:49 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-13 14:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-13 13:03 Clustered tables improves perfs ? Patrice Castet <pcastet@agematis.com>
2007-09-13 13:31 ` Alexander Staubo <alex@purefiction.net>
2007-09-13 14:49 ` Chris Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox