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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
