On Wed, 5 Mar 2003 09:47:51 -0000, "Paul McKay"
<paul_mckay@clearwater-it.co.uk> wrote:
>Hash Join (cost=1532.83..345460.73 rows=75115 width=23) (actual
>time=1769.84..66687.11 rows=16094 loops=1)
> -> Seq Scan on measurement (cost=0.00..336706.07 rows=418859
>width=15) (actual time=1280.11..59985.47 rows=455788 loops=1)
> -> Hash (cost=1498.21..1498.21 rows=13848 width=8) (actual
>time=253.49..253.49 rows=0 loops=1)
> -> Seq Scan on panconversation (cost=0.00..1498.21 rows=13848
>width=8) (actual time=15.64..223.18 rows=13475 loops=1)
>Total runtime: 66694.82 msec
|clearview=# select count(*) from measurement;
| 15302138
|clearview=# select count(*) from panconversation;
| 77217
Paul,
you seem to have a lot of dead tuples in your tables.
VACUUM FULL VERBOSE ANALYZE panconversation;
VACUUM FULL VERBOSE ANALYZE measurement;
This should cut your query time to ca. one third. If you could
migrate to 7.3 and create your tables WITHOUT OIDS, I'd expect a
further speed increase of ~ 15%.
Servus
Manfred