Skip site navigation (1) Skip section navigation (2)

Re: Comparitive UPDATE speed

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Andrew Sullivan <andrew(at)libertyrms(dot)info>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Comparitive UPDATE speed
Date: 2002-10-04 19:41:14
Message-ID: 26113.1033760474@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2002-10-04 19:45:28
Subject: Re: Comparitive UPDATE speed
Previous:From: Andrew SullivanDate: 2002-10-04 19:24:15
Subject: Re: Comparitive UPDATE speed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group