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

[David Jarvis <thangalin@xxxxxxxxx>]

Hi,

Hmm, that's nice, though I cannot but wonder whether the exclusive lock
required by CLUSTER is going to be a problem in the long run.

Not an issue; the inserts are one-time (or very rare; at most: once a year).
 

Hm, keep in mind that if the station clause alone is not selective
enough, scanning it may be too expensive.  The current three column

The seven child tables (split on category ID) have the following indexes:

1. Primary key (unique ID, sequence)
2. Station ID (table data is physically inserted by station ID order)
3. Station ID, Date, and Category ID (this index is CLUSTER'ed)
   

I agree that the last index is probably all that is necessary. 99% of the searches use the station ID, date, and category. I don't think PostgreSQL necessarily uses that last index, though.

Dave