From: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Long Running Update
Date: Fri, 24 Jun 2011 12:16:32 +0100
Message-ID: <4E047210.4020400@riskcontrollimited.com> (raw)
In-Reply-To: <4E034E78020000250003EB37@gw.wicourts.gov>
References: <4E035649.20007@riskcontrollimited.com>
<4E034E78020000250003EB37@gw.wicourts.gov>
Thank you Kevin.
> SET field_1 = table_B.field_1, field_2 = table_B.field_2
I will try that, if I have to next time.
> add something like this toyour WHERE clause,
> to prevent unnecessary updates:
>
> AND (table_B.field_1 IS DISTINCT FROM table_A.field_1
> OR table_B.field_2 IS DISTINCT FROM table_A.field_2);
Thank you for that explanation - I will keep that in mind in future. (In
this case, the two fields that are being updated are all known to be
empty - hence, distinct - in the target table.)
> 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?
Kind regards
Harry Mantheakis
London, UK
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: harry.mantheakis@riskcontrollimited.com, Kevin.Grittner@wicourts.gov
Subject: Re: Long Running Update
In-Reply-To: <4E047210.4020400@riskcontrollimited.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox