On Fri, Jun 19, 2009 at 3:26 PM, Tom Lane<tgl@xxxxxxxxxxxxx> wrote: > > That's the problem then. Notice what the query plan is doing: it's > scanning the table in order by ts_id, looking for the first row that > falls within the ts_interval_start_time range. Evidently this > particular range is associated with smaller ts_ids, so you reach it a > lot sooner in a ts_id ascending scan than a ts_id descending one. > > Given the estimated size of the range, scanning with the > ts_interval_start_time index wouldn't be much fun either, since it would > have to examine all rows in the range to determine the min or max ts_id. > You could possibly twiddle the cost constants to make the planner choose > that plan instead, but it's still not going to be exactly speedy. If your range of ts_interval_start_time is relatively static -- it doesn't look like it in this case given that's only an hour, but... -- then one option is to create a partial index on "ts_id" with the condition "WHERE ts_interval_start_time >= 'foo' AND ts_interval_start_time < 'bar' ". But if your range of times is always going to vary then you're going to have a problem there. There ought to be a way to use GIST to do this but I don't think we have any way to combine two different columns of different types in a single GIST index except as a multicolumn index which doesn't do what you want. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
