The main problem was that there wasn't an index on cal_qat_1. The
other indexes were fine so I don't know what happened to the first
one...
Nevertheless, it still takes longer than I would like. As requested:
telemetry=> explain analyze select tq1.*, tq2.* from
telemetry-> cal_quat_1 tq1, cal_quat_2 tq2
telemetry-> where tq1.timestamp = tq2.timestamp
telemetry-> and tq1.timestamp > '2004-01-12 09:47:56.0000
+0'::timestamp with time zone
telemetry-> and tq1.timestamp < '2004-01-12 09:50:44.7187
+0'::timestamp with time zone
telemetry-> order by tq1.timestamp;
NOTICE: QUERY PLAN:
Merge Join (cost=517417.89..2795472.80 rows=177664640 width=32)
(actual time=64878.04..64936.41 rows=142 loops=1)
-> Index Scan using cal_quat_1_timestamp on cal_quat_1 tq1
(cost=0.00..50549.03 rows=13329 width=16) (actual time=73.29..129.66
rows=142 loops=1)
-> Sort (cost=517417.89..517417.89 rows=2665818 width=16) (actual
time=62310.53..63727.33 rows=1020155 loops=1)
-> Seq Scan on cal_quat_2 tq2 (cost=0.00..43638.18
rows=2665818 width=16) (actual time=14.12..13462.19 rows=2665818
loops=1)
Total runtime: 65424.79 msec
Each table currently has 2665818 rows but grows by 86400/day. With
regards to hardware, the machine is a Sunfire 3600 (4 x 750MHz, 4GB
RAM, DB is on on a fiber channel disk array).
We are using 7.2.1.
Cheers,
Randall
