CODEWITHSUNDEEP
postgresqltriggers
Akkusativobjekt
Akkusativobjekt

Reputation: 2023

Best way to keep column value up to date

I have the following tables (simplified) :
enter image description here

enter image description here

They are connected with a foreign key ( element_id). If all acquisition entries of an element have a delivery_time which is greater than 28, the element gets the status critical. At the moment i use a view based on the elements table which checks for every element_id if it is critical. The function i use reads out the min(dilvery_time) of an element and checks if it is greater than 28. This calculation is done every time the view is opened.

The soltution works, but it's slow. Also i think the approach above does much unnecessary work, because the critical status can only changes if the table acquisition is modified.

My new approach would be to add a boolean column "critical" to the elements table. Also i would set up a trigger function on the acquisitions table which updates the critical status of the modified element (if necessary). Then the critical status should always be up to date and the selects should be much faster.

Is my new approach suitable, or are there better ways to solve my problem ?

Edit, here are the create statemens of the tables,view and function:

CREATE TABLE elements (
element_id serial primary key,
elemnt_name varchar(100));

CREATE TABLE acquisitions (
acquisition_id serial primary key,
element_id int NOT NULL,
delivery_time int,
foreign key (element_id) references elements(element_id));

CREATE OR REPLACE FUNCTION is_element_critical(param integer)
RETURNS boolean AS
$BODY$
DECLARE
delivery_date_int integer;
BEGIN
SELECT into delivery_date_int min(delivery_time) 
from acquisitions where   element_id  = param;
IF delivery_date_int > 28 THEN
RETURN true;
ELSE
return false;
END IF;
END
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE OR REPLACE VIEW elementview AS 
SELECT elements.element_id,
elements.elemnt_name, is_element_critical(elements.element_id)  
AS is_element_critical
FROM elements;

With ~10000 acquisitions and ~ 1500 elements a select on the elementview takes 1600 ms.

Upvotes: 0

Views: 59

Answers (1)

user330315
user330315

Reputation:

One problem with your approach is that the function is evaluated for each row in the view.

You could try to use a join and process this in a set-based manner (which is very often a better approach than a row-by-row processing).

CREATE OR REPLACE VIEW elementview 
AS 
SELECT e.element_id, 
       e.elemnt_name, 
       min(a.delivery_time)  > 28 as is_element_critical
FROM elements e
  JOIN acquisitions a ON a.element_id = e.element_id
GROUP BY e.element_id, e.elemnt_name;

Adding an index on acquisitions(element_id, delivery_time) might speed up this query.

If you don't have an acquisition for each element you might want to change this to an LEFT JOIN.

If the number of acquisitions that are not critical is much lower than those that are critical, you might be able to speed this up even further using a partial index:

create index idx_ac on acquisitions (element_id, delivery_time)
  where delivery_time > 28;

And then only join against acquisitions that are critical:

SELECT e.element_id, 
       e.elemnt_name, 
       min(a.delivery_time) > 28 as is_element_critical
FROM elements e
  LEFT JOIN acquisitions a ON a.element_id = e.element_id and a.delivery_time > 28
GROUP BY e.element_id, e.elemnt_name;

The left join is necessary because of the added condition and a.delivery_time > 28.

On my laptop the first query runs in 35ms (2000 elements, 30000 acquisitions). The second one runs in 5ms. Every element has at least one acquisition that is critical (which is probably not really realistic)

Upvotes: 2

Related Questions