By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,661 Members | 1,330 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,661 IT Pros & Developers. It's quick & easy.

performance versus order of fields in row

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"D. Stimits" <st*****@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

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

Nov 12 '05 #2

P: n/a
Tom Lane wrote:
"D. Stimits" <st*****@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. #
#================================================= = Ja******@Yahoo.com #


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

Nov 12 '05 #3

P: n/a
Jan Wieck <Ja******@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.