[postgis-users] Query performance issue

[Stephen Woodbridge]

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 
moment.

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).

-Steve