Re: Slow Query

Ok,

The query just ran and here is the basic output:

UPDATE 15445
Time: 22121.141 ms

and

explain ANALYZE update shawns_data set alias = null;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on shawns_data (cost=0.00..465.45 rows=15445 width=480) (actual time=0.034..67.743 rows=15445 loops=1)
Total runtime: 1865.002 ms
(2 rows)

Shawn

On Sat, 1 Sep 2007 13:18:16 -0700
Shawn <postgres(at)xmtservices(dot)net> wrote:

>
> Hi Tom,
>
> Thanks for replying.
>
> There are no FK's, indexes, or dependents on the alias field.
>
> The system is in the middle of its weekly full activity log resync,
> about 600 Million records. It will be done later this evening and I
> will run the explain analyze thenand I will post the results. I will
> also run a vacuum full analyze on it before the run and have timing
> on.
>
> Shawn
>
>
> On Sat, 01 Sep 2007 14:09:54 -0400 Tom Lane
> <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > Shawn <postgres(at)xmtservices(dot)net> writes:
> > > update shawns_data set alias = null;
> >
> > > Alias is a type varchar(8)
> >
> > > The table has 26 fields per record and there are about 15,700
> > > records. The server hardware is a dual QUAD-CORE Intel 2 GHz XEON
> > > dell 2950 server with 4 drive SAS RAID-5 array, and 16G of RAM.
> > > The OS is Slackware 11 with some updatews and Postgres v8.2.4
> > > built from source.
> >
> > > Even after VACUUM this simple line takes 35 sec to complete.
> >
> > Seems like a lot. Table bloat maybe (what does VACUUM VERBOSE say
> > about this table)? An unreasonably large number of indexes to
> > update? Foreign key checks? (Though unless you have FKs pointing
> > at alias, I'd think 8.2 would avoid needing to make any FK checks.)
> >
> > Could we see EXPLAIN ANALYZE output for this operation? (If you
> > don't really want to zap the column right now, wrap the EXPLAIN in
> > BEGIN/ROLLBACK.)
> >
> > regards, tom lane
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 6: explain analyze is your
> > friend
> >
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: if posting/reading
> through Usenet, please send an appropriate subscribe-nomail command
> to majordomo(at)postgresql(dot)org so that your message can get through to
> the mailing list cleanly
>