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

David Jarvis <thangalin@gmail.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