Re: Query Performance with Indexes on Integer type vs. Date type.

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(dot)kiula(at)gmail(dot)com>wrote:

> On Thu, Apr 28, 2011 at 12:17 AM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> >
> > Dhimant Patel <drp4kri(at)gmail(dot)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.
>