Re: Slow query performance on large table

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