Thread: PostgreSQL index issue

Hi Fellows

I have a question regarding PostgreSQL 9.1 indexing.

I am having a table and want to create a index for a column and I want to store the data with time zone for that column. The questions are:

1. Can I create a index for a column which store time stamp with time zone. If can is there ant performance issues?

2. Also I can store the time stamp value with zone as a long integer value. If so what is the difference between the above step. Which one is better.

Many Thanks.

Roshan

mperformer wrote:
> I have a question regarding PostgreSQL 9.1 indexing.
>
> I am having a table and want to create a index for a column and I want
to store the data with time
> zone for that column. The questions are:
>
> 1. Can I create a index for a column which store time stamp with time
zone. If can is there ant
> performance issues?

Yes, you can create an index on a TIMESTAMP WITH TIME ZONE column.
There are no performance problems except the ones that always
come with an index: INSERTs, UPDATEs and DELETEs will be slower
and do more disk I/O and locking.

> 2. Also I can store the time stamp value with zone as a long integer
value. If so what is the
> difference between the above step. Which one is better.

The smaller the indexed column is, the smaller and faster the
index will be. A timestamp uses 8 bytes, same as a bigint, so
that shouldn't matter.

Use the representation that is most useful to your processing.
For timestamps, this is usually the timestamp data type (which
automatically rejects impossible dates and provides date
arithmetic).

Yours,
Laurenz Albe

Hi Laurenz

Many thanks for your reply.

Could you please bit more explain about the following sentence you wrote:

There are no performance problems except the ones that always come with an
index: INSERTs, UPDATEs and DELETEs will be slower and do more disk I/O and
locking.

Many Thanks

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-index-issue-tp5716336p5716459.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 07/13/2012 06:06 AM, codevally wrote:
> Hi Laurenz
>
> Many thanks for your reply.
>
> Could you please bit more explain about the following sentence you wrote:
>
> There are no performance problems except the ones that always come with an
> index: INSERTs, UPDATEs and DELETEs will be slower and do more disk I/O and
> locking.

Every index you add slows down modifications to the table a little bit,
because it has to be kept up to date. It also uses more disk space and
takes time for VACCUM.

--
Craig Ringer