performance versus order of fields in row

[D. Stimits]

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.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Tom Lane]

Re: performance versus order of fields in row

"D. Stimits" <stimits@comcas t.net> writes:[color=blue]
> 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?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jan Wieck]

Re: performance versus order of fields in row

Tom Lane wrote:
[color=blue]
>"D. Stimits" <stimits@comcas t.net> writes:
>
>[color=green]
>>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?
>>
>>[/color]
>
>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.
>[/color]

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 #




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: performance versus order of fields in row

Jan Wieck <JanWieck@Yahoo .com> writes:[color=blue]
> Tom Lane wrote:[color=green]
>> 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.[/color][/color]
[color=blue]
> Do we still "cache" field offsets for not-nullable-fixed-size columns?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org