Re: position in DDL of columns used in indexes

Re: position in DDL of columns used in indexes

Matthew Wakeling  writes:
> 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.

Yeah.  There can be a small performance advantage to putting the more
frequently accessed columns first (so you don't have to skip over other
columns to get to them).  This has nothing directly to do with whether
they are indexed, though.

			regards, tom lane

Query logging time, not values

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

position in DDL of columns used in indexes