Hi all, I believe I understand the principle of clustering pretty well - physically reordering the records on the disk so the related rows, likely to be relevant to the same query, are more likely to be on the same page (rather than scattered) thereby reducing page lookups from the disk for queries utilizing that index.

I typically read people saying that clustered indexes are most useful in range lookups - eg. date ranges or number ranges (eg. "where mydate between '2001/01/01' and '2001/01/03'").

Would I be correct in saying that it could help improve performance in child/parent tables as well? For example, if the parent table "invoices" had a child table "invoice_items" (with foreign key "invoice_id"), then clustering the "invoice_items" table on field "invoice_id" would increase the likelihood that all the invoice_items rows for a specific invoices row would be stored on a single page.

Sound about right?

Thanks in advance,
Brendan

While you might see some improvement by clustering, it's not going to give you what you're looking for by itself. In fact, since you're pulling a single row at a time, I doubt it will do anything, except for reports.

You will get better improvement by moving one of the tables to a second physical disk.

Unfortunately, Postgres doesn't seem to support it, but something like Informix's FRAGMENT BY, or SQL Server's DPV's would give you rocking performance. Alas, maybe next year.