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
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