Need help with query
От | Kevin Heflin |
---|---|
Тема | Need help with query |
Дата | |
Msg-id | 200104172349.SAA14355@mercury.shreve.net обсуждение исходный текст |
Ответ на | Re: scan.c:900: warning: ... postgresql-7.1 (Limin Liu <limin@pumpkinnet.com>) |
Ответы |
Re: Need help with query
(Joel Burton <jburton@scw.org>)
|
Список | pgsql-general |
Don't know if this would be the correct place to ask this. We have a postgresql server running version 6.5.3 It hosts a 100 or so different databases, and performs just great. I do however have one query which takes about 20 seconds to complete. The database only has 200-300 records. Don't know much about SQL other than simple selects and inserts. I'm the one who created the query in question, and even today when I look at it, I'm not sure I understand it, but it gives us the results we want, it's just very slow. I'm sure someone with some SQL know-how could probably shorten this query down some. But it's way over my head. Basically I've got a database with these tables: client (list of clients) project (each project belongs to a client) subproject (each subproject belongs to a project) status (each project has a status) users (each project and subproject are assigned to a user) timelog (each timelog record indicates time worked on a particular subproject and who worked on it) A sample row would look like this: project_id|project_title|clientid|status|percentcomplete|duedate |usersid|client_id|name |users_id|users_name|status_id|status_name|hoursworked ----------+-------------+--------+------+---------------+----------+-------+ ---------+-----------+--------+----------+---------+-----------+----------- 143|project 1 | 79| 3| 100|04-06-2001| 8| 79|Client Name| 8| Kevin| 3|status | 1 The query is below. Again if this is not the appropriate list for such a question, my apologies. Kevin ******************* SELECT p1.project_id, p1.project_title, p1.clientid, p1.status, p1.percentcomplete, p1.duedate, p1.usersid, c2.client_id, c2.name, u3.users_id, u3.users_name, s4.status_id, s4.status_name, sum(case when t.jobid=s.subproject_id then t.timespent else 0::float4 end) as hoursworked FROM project p1, client c2, users u3, status s4, timelog t, subproject s WHERE (s4.status_id=2 OR s4.status_id=3) AND (c2.client_id=p1.clientid) AND (u3.users_id=p1.usersid) AND (s4.status_id=p1.status) AND (p1.project_id=s.projectid) GROUP BY p1.project_id, p1.project_title, p1.clientid, p1.status, p1.percentcomplete, p1.duedate, p1.usersid, c2.client_id, c2.name, u3.users_id, u3.users_name, s4.status_id, s4.status_name UNION SELECT p1.project_id, p1.project_title, p1.clientid, p1.status, p1.percentcomplete, p1.duedate, p1.usersid, c2.client_id, c2.name, u3.users_id, u3.users_name, s4.status_id, s4.status_name, 0 as hoursworked FROM project p1,client c2,users u3,status s4, subproject s WHERE (s4.status_id=2 OR s4.status_id=3) AND (c2.client_id=p1.clientid) AND (u3.users_id=p1.usersid) AND (s4.status_id=p1.status) AND NOT EXISTS (Select s.subproject_id from subproject s where s.projectid=p1.project_id) GROUP BY p1.project_id, p1.project_title, p1.clientid, p1.status, p1.percentcomplete, p1.duedate, p1.usersid, c2.client_id, c2.name, u3.users_id, u3.users_name, s4.status_id, s4.status_name ORDER BY p1.project_title
В списке pgsql-general по дате отправления: