Re: Comparitive UPDATE speed

Andrew,

> Have you run this with EXPLAIN ANALYSE?  It will actually perform the
> necessary steps, so it will reveal if the planner is getting
> something wrong.

Here it is:

Hash Join  (cost=3076.10..91842.88 rows=108648 width=40) (actual
time=18625.19..22823.39 rows=108546 loops=1)
  ->  Seq Scan on elbs_matter_links eml  (cost=0.00..85641.87 rows=117787
width=20) (actual time=18007.69..19515.63 rows=117787 loops=1)
  ->  Hash  (cost=2804.48..2804.48 rows=108648 width=20) (actual
time=602.12..602.12 rows=0 loops=1)
        ->  Seq Scan on case_clients cc  (cost=0.00..2804.48 rows=108648
width=20) (actual time=5.18..370.68 rows=108648 loops=1)
Total runtime: 22879.26 msec

The above doesn't seem bad, except that this is some serious hardware in this
system and 23 seconds right after VACUUM ANALYZE is too long.  I've a feeling
that I botched one of my postgresql.conf parameters or something.

I'll do an explain for the UPDATE query later, when the users are off the
system.

-Josh Berkus

--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco