Re: Comparitive UPDATE speed

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> 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

Hm. Why does it take 19500 milliseconds to read 117787 rows from
elbs_matter_links, if 108648 rows can be read from case_clients in 370
msec? And why does the output show that the very first of those rows
was returned only after 18000 msec?

I am suspicious that this table has a huge number of empty pages in it,
mostly at the beginning. If so, a VACUUM FULL would help. (Try
"vacuum full verbose elbs_matter_links" and see if it indicates it's
reclaiming any large number of pages.)

If that proves to be the answer, you need to look to your FSM
parameters, and perhaps arrange for more frequent regular vacuums
of this table.

regards, tom lane