DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=HBuVP1VauS1ARQaSmI13FrY8RsEVaWs8Wir4TOe8cuJ1EdyiWAba9bmlH/aX/JEOim
	3SeRx0+bCvrUu0jfuLHwL/UDiQIwwS2ynBUe6AzzvCi7lUOyhPkzjMaj3DyzO10ElO8s
	wiVRucy6F1oMS3TCLz3cpz8lCX3ITVhgQ8DEQ=
Message-ID: <4E0478CB.8080002@gmail.com>
Date: Fri, 24 Jun 2011 14:45:15 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Long Running Update
References: <4E035649.20007@riskcontrollimited.com>
	<4E034E78020000250003EB37@gw.wicourts.gov>
	<4E047210.4020400@riskcontrollimited.com>
In-Reply-To: <4E047210.4020400@riskcontrollimited.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

24.06.11 14:16, Harry Mantheakis написав(ла):
>
> > EXPLAIN the statement
>
> Here is the EXPLAIN result:
>
> ----------------------------------------------------------------------
> QUERY PLAN
> ----------------------------------------------------------------------
> Hash Join (cost=2589312.08..16596998.47 rows=74558048 width=63)
> Hash Cond: (table_A.id = table_B.id)
> -> Seq Scan on table_A(cost=0.00..1941825.05 rows=95612705 width=47)
> -> Hash (cost=1220472.48..1220472.48 rows=74558048 width=20)
> -> Seq Scan on table_B(cost=0.00..1220472.48 rows=74558048 width=20)
> ----------------------------------------------------------------------
>
> The documentation says the 'cost' numbers are 'units of disk page 
> fetches'.
>
> Do you, by any chance, have any notion of how many disk page fetches 
> can be processed per second in practice - at least a rough idea?
>
> IOW how do I convert - guesstimate! - these numbers into (plausible) 
> time values?
No chance. This are "virtual values" for planner only.
If I read correctly, your query should go into two phases: build hash 
map on one table, then update second table using the map. Not that this 
all valid unless you have any constraints (including foreign checks, 
both sides) to check on any field of updated table. If you have, you'd 
better drop them.
Anyway, this is two seq. scans. For a long query I am using a tool like 
ktrace (freebsd) to get system read/write calls backend is doing. Then 
with catalog tables you can map file names to relations 
(tables/indexes). Then you can see which stage you are on and how fast 
is it doing.
Note that partially cached tables are awful (in FreeBSD, dunno for 
linux) for such a query - I suppose this is because instead on 
sequential read, you get a lot of random reads that fools prefetch 
logic. "dd if=table_file of=/dev/null bs=8m" helps me a lot. You can see 
it it helps if CPU time goes up.

Best regards, Vitalii Tymchyshyn