Thanks for all valuable insights. I decided to drop the idea of adding additional column and will just rely on Date column for all ordering.
Tom - thanks for clear answer on the issue I was concerned about. Maciek,Kevin - thanks for ideas, hint on generate_series() - I will have to go through cpl of times of postgres documentation before I will have better grasp of all available tools but this forum is very valuable. -DP. On Wed, Apr 27, 2011 at 12:46 PM, Phoenix Kiula <phoenix.ki...@gmail.com>wrote: > On Thu, Apr 28, 2011 at 12:17 AM, Kevin Grittner > <kevin.gritt...@wicourts.gov> wrote: > > > > Dhimant Patel <drp4...@gmail.com> wrote: > > > > > I am a new comer on postgres world and now using it for some > > > serious (at least for me) projects. I have a need where I am > > > running some analytical + aggregate functions on data where > > > ordering is done on Date type column. > > > > > > From my initial read on documentation I believe internally a date > > > type is represented by integer type of data. This makes me wonder > > > would it make any good to create additional column of Integer type > > > and update it as data gets added and use this integer column for > > > all ordering purposes for my sqls - or should I not hasitate using > > > Date type straight into my sql for ordering? > > > > I doubt that this will improve performance, particularly if you ever > > want to see your dates formatted as dates. > > > > > Better yet, is there anyway I can verify impact of ordering on > > > Date type vs. Integer type, apart from using \timing and explain > > > plan? > > > > You might be better off just writing the code in the most natural > > way, using the date type for dates, and then asking about any > > queries which aren't performing as you hope they would. Premature > > optimization is often counter-productive. If you really want to do > > some benchmarking of relative comparison speeds, though, see the > > generate_series function -- it can be good at generating test tables > > for such things. > > > > > There is a lot of really good advice here already. I'll just add one > thought. > > If the dates in your tables are static based only on creation (as in > only a CREATE_DATE, which will never be modified per row like a > MODIFY_DATE for each record), then your thought might have made sense. > But in that case you can already use the ID field if you have one? > > In most real world cases however the DATE field will likely be storing > an update time as well. Which would make your thought about numbering > with integers pointless. >
