Excerpts from David Jarvis's message of mar jun 01 14:01:22 -0400 2010: > Sorry, Alvaro. > > I was contemplating using a GIN or GiST index as a way of optimizing the > query. My fault -- I didn't read the whole thread. > 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. 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. > 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. Hm, keep in mind that if the station clause alone is not selective enough, scanning it may be too expensive. The current three column index is probably a lot faster to search (though of course it's causing more work to be kept up to date on insertions). -- Álvaro Herrera <alvherre@xxxxxxxxxxxxxxxxx> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
