Skip site navigation (1) Skip section navigation (2)

Re: Optimize date query for large child tables: GiST or GIN?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Jarvis <thangalin(at)gmail(dot)com>
Cc: Thom Brown <thombrown(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-20 19:52:50
Message-ID: 7767.1274385170@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
David Jarvis <thangalin(at)gmail(dot)com> writes:
> I was thinking that I could add three more columns to the measurement table:
> year_taken, month_taken, day_taken
> Then index those. That should allow me to avoid extracting years, months,
> and days from the *m.taken* date column.

You could, but I don't think there's any advantage to that versus
putting indexes on extract(day from taken) etc.  The extra fields
eat more space in the table proper, and the functional index isn't
really any more expensive than a plain index.  Not to mention that
you can have bugs with changing the date and forgetting to update
the derived columns, etc etc.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: David JarvisDate: 2010-05-20 19:58:16
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Previous:From: David JarvisDate: 2010-05-20 19:45:53
Subject: Re: Optimize date query for large child tables: GiST or GIN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group