Thread: performance versus order of fields in row

I'm not looking for an exact answer here, but instead something more
"rule of thumb". If I have a table with many fields, and I retrieving
small groups of fields during a SELECT, whereby the groups of fields are
indexed and/or clustered, will I get a faster select in the left-most
fields, or the right-most fields? Or will it not matter? It would be
unusual to SELECT *, I expect to be selecting groups of 4 to 16 fields,
and I am wondering if the most often occuring queries might be improved
by placing them at left or right ends of the table (or if there is any
help at all doing this). The selected groups of fields are unlikely to
be used as a search criterion, but instead as simple read-only, while
other fields determine if the row will be included.

"D. Stimits" <stimits@comcast.net> writes:
> I'm not looking for an exact answer here, but instead something more
> "rule of thumb". If I have a table with many fields, and I retrieving
> small groups of fields during a SELECT, whereby the groups of fields are
> indexed and/or clustered, will I get a faster select in the left-most
> fields, or the right-most fields? Or will it not matter?

Fields earlier in the table definition (further to the left) are
marginally faster to access than ones further to the right.  I doubt it
would be real noticeable unless you had hundreds of fields altogether.

            regards, tom lane

Tom Lane wrote:

>"D. Stimits" <stimits@comcast.net> writes:
>
>
>>I'm not looking for an exact answer here, but instead something more
>>"rule of thumb". If I have a table with many fields, and I retrieving
>>small groups of fields during a SELECT, whereby the groups of fields are
>>indexed and/or clustered, will I get a faster select in the left-most
>>fields, or the right-most fields? Or will it not matter?
>>
>>
>
>Fields earlier in the table definition (further to the left) are
>marginally faster to access than ones further to the right.  I doubt it
>would be real noticeable unless you had hundreds of fields altogether.
>

Do we still "cache" field offsets for not-nullable-fixed-size columns?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:
> Tom Lane wrote:
>> Fields earlier in the table definition (further to the left) are
>> marginally faster to access than ones further to the right.  I doubt it
>> would be real noticeable unless you had hundreds of fields altogether.

> Do we still "cache" field offsets for not-nullable-fixed-size columns?

Yeah, we do, but I didn't think he wanted to get into that level of
detail ... in any case it's a safe bet that earlier fields are no slower
than later ones.

            regards, tom lane