September 19th, 2003, 12:35 PM
Query Optimization: subqueries, aggregates
This is gonna be a long post I can feel it, so please stick with me .
I have a table that looks like
This lists all related events to a particular incident. I want to get all incidents that haven't had a resolving event and also know what the particular event the an incident is at for each incident.
CREATE TABLE "incident_event" (
"inc_id" int NOT NULL REFERENCES "incident" ("inc_id") ON DELETE CASCADE ON UPDATE CASCADE,
"evttype_id" int NOT NULL REFERENCES "event_type" ("evttype_id") ON DELETE CASCADE ON UPDATE CASCADE,
"evtdate" timestamp NOT NULL,
"date_update" timestamp NOT NULL DEFAULT now(),
"userid" int4 NOT NULL,
PRIMARY KEY ("inc_id", "evttype_id")
I did this first by doing the following query:
Which basically retrieved all events for each unresolved incident which could then be parsed then parsed by PHP. I then came up with the following query which uses an aggregate (MAX) so that I can retreive the exact data I want without having to do extra processing on the client side which looks like:
SELECT incident.inc_id, pat_info.pat_name_first || ' ' || pat_info.pat_name_mi || ' ' || pat_info.pat_name_last AS fullname, event_type.evt_desc, incident_event.evtdate
FROM incident_event, event_type, pat_info, incident
WHERE incident_event.evttype_id = event_type.evttype_id AND incident.inc_id = incident_event.inc_id AND pat_info.pat_id = incident.pat_id AND incident.inc_id NOT IN
(SELECT incident.inc_id FROM incident, event_type, incident_event WHERE incident.inc_id = incident_event.inc_id AND incident_event.evttype_id = event_type.evttype_id AND (event_type.evt_desc = 'DEPARTURE' OR event_type.evt_desc = 'LWBS') ) ORDER BY inc_id ASC, evtdate DESC, event_type.evttype_id DESC;
The second query yields a better EXPLAIN output (no EXPLAIN ANALYZE as I'm still on 7.1.3) probably because of the removed sorting. And it gives me the exact data I want. This is good since I believe (could be wrong) that aggregates may have better performance in the upcoming 7.4 (when I'll upgrade).
SELECT incomp_evt.inc_id, pat_info.pat_name_first || ' ' || pat_info.pat_name_mi || ' ' || pat_info.pat_name_last AS fullname, event_type.evt_desc, incomp_evt.evtdate
(SELECT incomplete.*, evtdate FROM (SELECT inc_id, MAX(evttype_id) AS evttype_id FROM incident_event WHERE inc_id NOT IN (SELECT incident_event.inc_id FROM event_type, incident_event WHERE incident_event.evttype_id = event_type.evttype_id AND (event_type.evt_desc = 'DEPARTURE' OR event_type.evt_desc = 'LWBS') ) GROUP BY inc_id) AS incomplete, incident_event WHERE incident_event.inc_id = incomplete.inc_id AND incident_event.evttype_id = incomplete.evttype_id) AS
incomp_evt, event_type, pat_info, incident
WHERE incomp_evt.evttype_id = event_type.evttype_id AND incident.inc_id = incomp_evt.inc_id AND pat_info.pat_id = incident.pat_id
I was just wondering if there was a better query than the 2nd one I've come up with. I'm not averse to PHP processing on the web server (for a faster, but less than perfect result set) as I'd really like to ease the load on the DB server as it gets kinda hammered at times. Any input would be greatly appreciated. I can provide more info on the DB setup or EXPLAIN output if helpful.
Last edited by bcyde; September 19th, 2003 at 12:38 PM.
PostgreSQL, it's what's for dinner...