Thread: I'm stumped
I can't figure out an efficient way to do this. Basically I had a typical 3-tier relationship: (Employee -> Department -> Division) However, at some point the need to move employees arose, but instead of changing the key in the emp table, we now have an over-ride table, so a history can be tracked. If I want to get the info for a particular employee, its a pretty simple deal, however, getting all the employees for a dept or division has become troublesome. A very simplified schema: divisions ( div_id, div_name ); departments ( dept_id, dept_name, div_id ); employees ( emp_id, emp_name, dept_id ); emp_dept ( emp_id, dept_id, active, changed_by, changed_when ); The original way that worked well: SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id= v.div_id INNER JOIN employees e ON e.dept_id = d.dept_id WHERE v.div_id = 123; What was initially tried: SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN employees e ON e.dept_id = d.dept_id LEFT JOINemp_dept ed ON ed.emp_id = e.emp_id AND ed.active = true WHERE v.div_id = 123; This query is flawed, as it still always puts the employees in their original div, but reports the new dept. Which we didn't catch as a problem until emps were moved to depts in a different division. I tried creating a function: CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS ' SELECT CASE WHEN ed.dept_id IS NOT NULL THEN ed.dept_id ELSE e.dept_id END FROM employees AS e LEFT JOIN emp_dept AS ed ON ed.emp_id = e.emp_id AND ed.active = true WHERE e.emp_id = $1 ' LANGUAGE SQL STABLE; And then tried: SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id= v.div_id INNER JOIN employees e ON get_empdept(e.emp_id) = d.dept_id WHERE v.div_id = 123; However since the function is not immutable (since it does a query), I can't create an index, and the join always does a seq scan. I also thought to create a view, but I don't believe Postgres supports indexed views. It was always using a seq scan too. The above examples are actually quite simplified, as several other tables get joined along the way, I'm not sure a UNION would work or not, how would it exclude the ones that match the dept_id in the emp table for those emps that match on the over-ride table? Any suggestions? Thanks
Doug Y a écrit : > I can't figure out an efficient way to do this. Basically I had a > typical 3-tier relationship: > (Employee -> Department -> Division) > However, at some point the need to move employees arose, but instead > of changing the key in the emp table, we now have an over-ride table, > so a history can be tracked. > > If I want to get the info for a particular employee, its a pretty > simple deal, however, getting all the employees for a dept or division > has become troublesome. > > A very simplified schema: > divisions ( div_id, div_name ); > departments ( dept_id, dept_name, div_id ); > employees ( emp_id, emp_name, dept_id ); > emp_dept ( emp_id, dept_id, active, changed_by, changed_when ); > > The original way that worked well: > SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name > FROM divisions v > INNER JOIN departments d > ON d.div_id = v.div_id > INNER JOIN employees e > ON e.dept_id = d.dept_id > WHERE v.div_id = 123; > > What was initially tried: > SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name > FROM divisions v > INNER JOIN departments d > ON d.div_id = v.div_id > INNER JOIN employees e > ON e.dept_id = d.dept_id > LEFT JOIN emp_dept ed > ON ed.emp_id = e.emp_id AND ed.active = true > WHERE v.div_id = 123; > This query is flawed, as it still always puts the employees in their > original div, but reports the new dept. Which we didn't catch as a > problem until emps were moved to depts in a different division. > > I tried creating a function: > CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS ' > SELECT CASE WHEN ed.dept_id IS NOT NULL > THEN ed.dept_id > ELSE e.dept_id END > FROM employees AS e > LEFT JOIN emp_dept AS ed > ON ed.emp_id = e.emp_id AND ed.active = true > WHERE e.emp_id = $1 > ' LANGUAGE SQL STABLE; > > And then tried: > SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name > FROM divisions v > INNER JOIN departments d > ON d.div_id = v.div_id > INNER JOIN employees e > ON get_empdept(e.emp_id) = d.dept_id > WHERE v.div_id = 123; > > However since the function is not immutable (since it does a query), I > can't create an index, and the join always does a seq scan. > > I also thought to create a view, but I don't believe Postgres supports > indexed views. It was always using a seq scan too. > > The above examples are actually quite simplified, as several other > tables get joined along the way, I'm not sure a UNION would work or > not, how would it exclude the ones that match the dept_id in the emp > table for those emps that match on the over-ride table? > > Any suggestions? > Hello, have you an index on emp_dept on emp_id, dept_id ? what about this ? SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN employees e ON e.dept_id = d.dept_id WHERE NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id) AND v.div_id = 2 UNION ALL SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name FROM divisions v INNER JOIN departments d ON d.div_id = v.div_id INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id INNER JOIN employees e ON e.emp_id = ed.emp_id WHERE ed.active=true AND v.div_id = 2 Regards, Thomas > Thanks > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Hi, I didn't bother analysing this too deeply, so keep that in mind when you read my reply ;-) However the point that set off alarm bells for me was this statement "we now have an over-ride table". It seems to me that you shouldn't be over-riding anything, and the emp_dept table should just be history - ie "emp_dept_his", and it wouldn't need the "active" column. If you need history then you could use a trigger or rule to insert the history record whenever the emloyee changes department. The key question here is "Why wouldn't you change the key in the emp table when that key represents the employee's department?". It seems like a handbook case to me but maybe I missed something. regards Iain >> I can't figure out an efficient way to do this. Basically I had a typical >> 3-tier relationship: >> (Employee -> Department -> Division) >> However, at some point the need to move employees arose, but instead of >> changing the key in the emp table, we now have an over-ride table, so a >> history can be tracked. >> >> If I want to get the info for a particular employee, its a pretty simple >> deal, however, getting all the employees for a dept or division has >> become troublesome. >> >> A very simplified schema: >> divisions ( div_id, div_name ); >> departments ( dept_id, dept_name, div_id ); >> employees ( emp_id, emp_name, dept_id ); >> emp_dept ( emp_id, dept_id, active, changed_by, changed_when ); >> >> The original way that worked well: >> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name >> FROM divisions v >> INNER JOIN departments d >> ON d.div_id = v.div_id >> INNER JOIN employees e >> ON e.dept_id = d.dept_id >> WHERE v.div_id = 123; >> >> What was initially tried: >> SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name >> FROM divisions v >> INNER JOIN departments d >> ON d.div_id = v.div_id >> INNER JOIN employees e >> ON e.dept_id = d.dept_id >> LEFT JOIN emp_dept ed >> ON ed.emp_id = e.emp_id AND ed.active = true >> WHERE v.div_id = 123; >> This query is flawed, as it still always puts the employees in their >> original div, but reports the new dept. Which we didn't catch as a >> problem until emps were moved to depts in a different division. >> >> I tried creating a function: >> CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS ' >> SELECT CASE WHEN ed.dept_id IS NOT NULL >> THEN ed.dept_id >> ELSE e.dept_id END >> FROM employees AS e >> LEFT JOIN emp_dept AS ed >> ON ed.emp_id = e.emp_id AND ed.active = true >> WHERE e.emp_id = $1 >> ' LANGUAGE SQL STABLE; >> >> And then tried: >> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name >> FROM divisions v >> INNER JOIN departments d >> ON d.div_id = v.div_id >> INNER JOIN employees e >> ON get_empdept(e.emp_id) = d.dept_id >> WHERE v.div_id = 123; >> >> However since the function is not immutable (since it does a query), I >> can't create an index, and the join always does a seq scan. >> >> I also thought to create a view, but I don't believe Postgres supports >> indexed views. It was always using a seq scan too. >> >> The above examples are actually quite simplified, as several other tables >> get joined along the way, I'm not sure a UNION would work or not, how >> would it exclude the ones that match the dept_id in the emp table for >> those emps that match on the over-ride table? >> >> Any suggestions? >> > Hello, > have you an index on emp_dept on emp_id, dept_id ? > what about this ? > > SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name > FROM divisions v > INNER JOIN departments d ON d.div_id = v.div_id > INNER JOIN employees e ON e.dept_id = d.dept_id > WHERE NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id) > AND v.div_id = 2 > UNION ALL > SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name > FROM divisions v > INNER JOIN departments d ON d.div_id = v.div_id > INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id > INNER JOIN employees e ON e.emp_id = ed.emp_id > WHERE ed.active=true > AND v.div_id = 2 > > Regards, > Thomas