Brian Cox <brian.cox@ca.com> wrote:
> Kevin Grittner [Kevin.Grittner@wicourts.gov] wrote:
>> Is there any correlation between ts_id and ts_interval_start_time?
> only vaguely: increasing ts_interval_start_time implies increasing
> ts_id but there may be many rows (100,000's) with the same
> ts_interval_start_time
>
>> Perhaps if you tried min and max with different time ranges it
>> would find a row on a backward scan faster. It'll take ten times
>> as long if it has to scan through ten times as many rows to find a
>> match.
> it looks like there are fewer rows backwards than forwards:
Hmmm.... I was going to suggest possible bloat near the end of the
table, but the vacuum and reindex should have kept that at from being
a problem.
This might be an issue where disks are laid out so that the pages can
be read from start to end quickly; reading backwards might cause a lot
more rotational delay.
>> I don't suppose you have an index on ts_interval_start_time?
> there is an index. I mentioned this in my orginal posting.
Sorry I missed that. I was afraid that it might not use it because
PostgreSQL doesn't yet recognize correlations between columns. If it
did, it might determine that the other index was better for this query
(which may or may not be the case).
Could you provide the output of VACUUM ANALYZE for these queries, so
we can compare expected to actual? Also, what is your statistics
target for these (default_statistics_target if you haven't overridden
the specific columns involved)?
I guess you could try something like this, too:
select max(ts_id) from (select ts_id from
ts_stats_transet_user_interval a
where 0=0 and a.ts_interval_start_time >= '2009-6-16 01:00' and
a.ts_interval_start_time < '2009-6-16 02:00') x;
(Untested, so you might need to fix some typo or oversight.)
The EXPLAIN ANALYZE of that might yield interesting information.
If that doesn't get you closer to something acceptable, you could
consider a functional index on the inverse of the ts_id column, and
search for the negative of the min of that. Kinda ugly, but it might
work because the disk would be spinning in the right direction for
you.
-Kevin