[postgis-users] Query performance issue
woodbri at swoodbridge.com
Thu Oct 13 12:05:44 PDT 2005
Michael Fuhr wrote:
> Another possibility would be to look at rows' xmax columns. As
> each row is updated, it should receive a new xmax value that's
> visible to other transactions, even while the update is still in
> progress. Count how many rows have the new value to see how many
> rows have been updated (it'll be changing, but at least you'll get
> an idea). Something like the following might do the trick:
> SELECT count(*) FROM streets WHERE xmax = (SELECT xmax FROM streets LIMIT 1);
> This assumes that "SELECT xmax FROM streets LIMIT 1" returns the
> transaction ID of the update. You could query pg_locks and
> pg_stat_activity (if you have stats_command_string enabled) to see
> if the process using that transaction ID is indeed the one doing
> the update.
Ack! I found the xmax value by watching the server status on locks and
which flashed the TX value since this is the only process running at the
SELECT count(*) FROM streets WHERE xmax = 71532;
count = 113,593 for 59,500 seconds into the run.
This is about 0.5 sec per row, and with 1,798,900 in the table that
works out to be 262 hours!!!
moving to plan C (ie: the next query attempt).
More information about the postgis-users