On Fri, Jun 19, 2009 at 3:26 PM, Tom Lane<tgl@sss.pgh.pa.us> 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