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@xmtservices.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@sss.pgh.pa.us> wrote:
>
> > Shawn <postgres@xmtservices.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@postgresql.org so that your message can get through to
> the mailing list cleanly
>