Re: position in DDL of columns used in indexes

On Thu, 8 Oct 2009, Michael Gould wrote:
> In other SQL engines that I've used, it is recommended that the columns that are used in
> various indexes be placed at the beginning of a row since at some point (depending on the
> engine and/or pagesize) wide rows could end up on other pages.  From a performance
> standpoint on large tables this makes a big difference.  Is the same true with Postgres. 
> Should I try and make sure that my indexes fit in the first 8192 bytes?

Interesting question. AFAIK (I'm not an expert, someone correct me):

Postgres does not split rows across multiple pages, so this should never
be a concern. When a row is too big for a page, Postgres will select the
larger of the columns from the row and compress them. If that fails to
bring the row size down, then Postgres will select the larger columns and
remove them to a separate storage area, and leave just the references in
the actual row. Therefore, the order of columns should not matter.

Moreover, whether a row is used in an index should not make any
difference. The index stores the values too, right? Postgres will look up
in the index, and then fetch the rows, in two separate operations.

Matthew

--
Let's say I go into a field and I hear "baa baa baa". Now, how do I work
out whether that was "baa" followed by "baa baa", or if it was "baa baa"
followed by "baa"?
- Computer Science Lecturer