Re: What are the benefits of using a clustered index?

Mike Christensen wrote:

>  However, if the query
> planner can't assume the data is in a certain order on disk, what's the
> point of having this at all?

One benefit is that it reduces the number of pages that must be read
from disk to retrieve all tuples that have a particular value for the
indexed field. Because the tuples tend to be clustered by (in your case)
recipeid, you'll normally have quite a few relevant tuples on a page and
won't have as much uninteresting and irrelevent data getting read in
along with what you actually want. That'll also improve efficiency of
memory/cache use.

I think it also helps keep the index smaller, since it doesn't need to
refer to as many pages for a given value of interest.

If you set a non-default FILLFACTOR on the table (and proably index) Pg
will leave gaps in the table and I think it will try to insert tuples to
maintain clustering order for smaller indexes and faster index scans.
The downside is that there are holes in the table that may slow down a
sequential scan somewhat.

--
Craig Ringer