1

I have a relation EnumeratedSensorReading that includes a siteid and a quantityid column. I want to replace these two columns with a single sensorid column. I have added this column, and it is currently empty.

The mapping from the (siteid, quantityid) tuple to sensorid is provided in a table SiteQuantitySensor. The UPDATE statement is as follows:

UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid 
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);

The EnumeratedSensorReading table has about 600K rows and the query seems to run forever. Here is the EXPLAIN output:

EXPLAIN UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Update on enumeratedsensorreading  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
   ->  Merge Join  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
         Merge Cond: ((t.quantityid = s.quantityid) AND (t.siteid = s.siteid))
         ->  Sort  (cost=96208.18..97767.86 rows=623872 width=14)
               Sort Key: t.quantityid, t.siteid
               ->  Seq Scan on enumeratedsensorreading t  (cost=0.00..14828.72 rows=623872 width=14)
         ->  Materialize  (cost=15073092.05..15328879.57 rows=51157504 width=46)
               ->  Sort  (cost=15073092.05..15200985.81 rows=51157504 width=46)
                     Sort Key: s.quantityid, s.siteid
                     ->  Nested Loop  (cost=0.00..654299.55 rows=51157504 width=46)
                           ->  Seq Scan on enumeratedsensorreading  (cost=0.00..14828.72 rows=623872 width=28)
                           ->  Materialize  (cost=0.00..2.23 rows=82 width=18)
                                 ->  Seq Scan on sitequantitysensor s  (cost=0.00..1.82 rows=82 width=18)

As I have very little experience in interpreting these results, could someone help me understand why the statement is so slow, and if there's anything I should do?

2

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow):

From the PostgreSQL documentation:

from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

AFAICT you are updating each row of enumeratedsensorreading with values obtained by performing an unqualified join (no WHERE-clause) of enumeratedsensorreading against (a qualified join of enumeratedsensorreading against sitequantitysensor (FROM-clause)). This will result in at least 600k updates per row (depending on whethe the relevant values in sitequantitysensor are distinct).

Simply removing the second join should do the trick:

WITH sensor_ids AS 
  (SELECT DISTINCT sensorid, siteid, quantityid FROM sitequantitysensor) 
UPDATE enumeratedsensorreading e 
  SET sensorid = s.sensorid 
  FROM sitequantitysensor s 
  WHERE s.siteid = e.siteid AND s.quantityid = e.quantityid;

The join you intended is already defined by FROM- and WHERE-clause

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.