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

Sorry, Alvaro.

I was contemplating using a GIN or GiST index as a way of optimizing the query.

Instead, I found that re-inserting the data in order of station ID (the primary look-up column) and then CLUSTER'ing on the station ID, taken date, and category index increased the speed by an order of magnitude.

I might be able to drop the station/taken/category index in favour of the simple station index and CLUSTER on that, instead (saving plenty of disk space). Either way, it's fast right now so I'm not keen to try and make it much faster.

Dave