#1
  1. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    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
    PHP Code:
    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")
    ); 
    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.

    I did this first by doing the following query:
    PHP Code:
    SELECT incident.inc_idpat_info.pat_name_first || ' ' || pat_info.pat_name_mi || ' ' || pat_info.pat_name_last AS fullnameevent_type.evt_descincident_event.evtdate 
    FROM incident_event
    event_typepat_infoincident 
    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 incidentevent_typeincident_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 ASCevtdate DESCevent_type.evttype_id DESC
    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:

    PHP Code:
    SELECT incomp_evt.inc_idpat_info.pat_name_first || ' ' || pat_info.pat_name_mi || ' ' || pat_info.pat_name_last AS fullnameevent_type.evt_descincomp_evt.evtdate 
    FROM
    (SELECT incomplete.*, evtdate FROM (SELECT inc_idMAX(evttype_id) AS evttype_id FROM incident_event WHERE inc_id NOT IN (SELECT incident_event.inc_id FROM event_typeincident_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 incompleteincident_event WHERE incident_event.inc_id incomplete.inc_id AND incident_event.evttype_id incomplete.evttype_id) AS 
    incomp_evtevent_typepat_infoincident
    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 
    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).

    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.

    Thanks,
    -b
    Last edited by bcyde; September 19th, 2003 at 01:38 PM.
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo