From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | "Paul McKay" <paul_mckay(at)clearwater-it(dot)co(dot)uk> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Slow query performance on large table |
Date: | 2003-03-08 10:15:56 |
Message-ID: | 2agj6vc16t42rar0hou13e0e2qcplqe9rj@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, 5 Mar 2003 09:47:51 -0000, "Paul McKay"
<paul_mckay(at)clearwater-it(dot)co(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Bruce Lynes | 2003-03-08 18:34:01 | Re: Stored Procedures and compiling |
Previous Message | Tom Lane | 2003-03-08 03:13:24 | Re: Stored Procedures and compiling |