From: | Kevin Heflin <kheflin(at)shreve(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Need help with query |
Date: | 2001-04-17 23:49:19 |
Message-ID: | 200104172349.SAA14355@mercury.shreve.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | clayton | 2001-04-18 01:12:40 | Re: [SQL] Where are the 7.1 RPM's? |
Previous Message | Lonnie Cumberland | 2001-04-17 23:30:43 | Where are the 7.1 RPM's? |