Re: Slow query performance on large table

On Tue, 2003-03-04 at 11:11, Paul McKay wrote:
> The results were
>
> clearview=# explain analyse
> clearview-# select assessment,time
> clearview-# from measurement
> clearview-# where assessment = 53661
> clearview-# and time between 1046184261 and 1046335461;
>
> NOTICE: QUERY PLAN:
>
> Index Scan using idx_measurement_assessment on measurement
> (cost=0.00..34668.61 rows=261 width=8) (actual time=26128.07..220584.69
> rows=503 loops=1)
> Total runtime: 220587.06 msec
>
> EXPLAIN
>
> After adding the index kindly suggested by yourself and Tomasz I get,
>
> clearview=# explain analyse
> clearview-# select assessment,time
> clearview-# from measurement
> clearview-# where assessment = 53661
> clearview-# and time between 1046184261 and 1046335461;
> NOTICE: QUERY PLAN:
>
> Index Scan using ind_measurement_ass_time on measurement
> (cost=0.00..1026.92 rows=261 width=8) (actual time=15.37..350.46
> rows=503 loops=1)
> Total runtime: 350.82 msec
>
> EXPLAIN
>
>
> I vaguely recall doing a bit of a reorganize on this database a bit back
> and it looks like I lost the primary Key index. No wonder it was going
> slow.
>

Maybe it's just me, but I get the feeling you need to work some regular
reindexing into your maintenance schedule. Given your query is using
between, I don't think it would use the index on the time field anyway
(and explain analyze seems to be supporting this). Rewrite it so that
you have a and time > foo and time < bar and I think you'll see a
difference. With that in mind, I think your speedier query results are
due more to having a non-bloated index freshly created than the fact
that it being a dual column index.

Robert Treat