Re: Slow query performance on large table

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

In response to

Browse pgsql-performance by date

  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