Postgres Pro
Downloads
Home   >   mailing lists

Thread: Speed issues with update

Speed issues with update

From
Lorenz Bateman
Date:
I'm using 'PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0, compiled by
cc'.

The problem is that the following statement takes approx 3hrs on a table
with approx 136,000 records.

update table_name set state = 10 where pid is null;

pid is indexed. The result from an explain on the statement is as
follows...

Seq Scan on table_name  (cost=0.00..8668.56 rows=45348 width=310)

When the query is running, disk io is minimal, but cpu is maxed.

Can anyone suggest a solution or a way of finding where pg is spending all
the cpu time?

Thanks

Loz

Re: Speed issues with update

From
Norman Clarke
Date:
Lorenz,

Have you run VACUUM on the table?

Norman

On Wednesday 01 November 2000 05:43 am, you wrote:
> I'm using 'PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0, compiled by
> cc'.
>
> The problem is that the following statement takes approx 3hrs on a table
> with approx 136,000 records.
>
> update table_name set state = 10 where pid is null;
>
> pid is indexed. The result from an explain on the statement is as
> follows...
>
> Seq Scan on table_name  (cost=0.00..8668.56 rows=45348 width=310)
>
> When the query is running, disk io is minimal, but cpu is maxed.
>
> Can anyone suggest a solution or a way of finding where pg is spending all
> the cpu time?
>
> Thanks
>
> Loz

Re: Speed issues with update

From
Lorenz Bateman
Date:
Yup, a full VACUUM analyze was done on the table. The problem seems to be
that 95% of processor time is spent in the kernal opening and closing the
same table file. This does seem a little strange. Could this be a problem
with the code? I've also tried a full reinstall, but to no avail.

Loz

On Thu, 2 Nov 2000, Norman Clarke wrote:

> Lorenz,
>
> Have you run VACUUM on the table?
>
> Norman
>
> On Wednesday 01 November 2000 05:43 am, you wrote:
> > I'm using 'PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0, compiled by
> > cc'.
> >
> > The problem is that the following statement takes approx 3hrs on a table
> > with approx 136,000 records.
> >
> > update table_name set state = 10 where pid is null;
> >
> > pid is indexed. The result from an explain on the statement is as
> > follows...
> >
> > Seq Scan on table_name  (cost=0.00..8668.56 rows=45348 width=310)
> >
> > When the query is running, disk io is minimal, but cpu is maxed.
> >
> > Can anyone suggest a solution or a way of finding where pg is spending all
> > the cpu time?
> >
> > Thanks
> >
> > Loz
>