improvements to query with hierarchical elements - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal">Greetings,<p class="MsoNormal"> <p class="MsoNormal">I have a complex query
whichI am trying to figure out the most efficient way of performing.<p class="MsoNormal"> <p class="MsoNormal">My
databaseis laid out as follows:<p class="MsoNormal">items –have_many-> events –have_many-> event_locations
–have_many->locations<p class="MsoNormal"> <p class="MsoNormal">also rows in the location_links table link two
locationstogether in a parent-child relationship and rows in the location_descendants table provide a full list of the
descendantsof a<p class="MsoNormal">particular location.<p class="MsoNormal"> <p class="MsoNormal">I am trying to find
alllocations which both are direct children of a given parent location, and are associated with at least one item in a
constrainedsubset of items.<p class="MsoNormal">(eg. Find all states of the USA in which at least one wooden axe was
made.Also find the number of wooden axes made in each state.)<p class="MsoNormal"> <p class="MsoNormal">I have
developedthe following query:<p class="MsoNormal"> <p class="MsoNormal">SELECT  locations.*,<p
class="MsoNormal">       location_ids.item_count AS item_count<p class="MsoNormal">FROM    locations<p
class="MsoNormal">       JOIN<p class="MsoNormal">                (SELECT immediate_descendants.ancestor_id AS id,<p
class="MsoNormal">                       COUNT(DISTINCT creation_events.item_id) AS item_count<p
class="MsoNormal">               FROM    event_locations<p class="MsoNormal">                        JOIN<p
class="MsoNormal">                               (SELECT *<p class="MsoNormal">                                FROM   
location_descendants<pclass="MsoNormal">                                WHERE   ancestor_id IN<p
class="MsoNormal">                                       (SELECT child_id<p
class="MsoNormal">                                       FROM    location_links<p
class="MsoNormal">                                       WHERE   parent_id = *<b>note 1</b>*<p
class="MsoNormal">                                       )<p class="MsoNormal">                                ) AS
immediate_descendants<pclass="MsoNormal">                        ON      event_locations.location_id =
immediate_descendants.descendant_id<pclass="MsoNormal">                        JOIN<p
class="MsoNormal">                               (SELECT *<p class="MsoNormal">                                FROM   
events<pclass="MsoNormal">                                WHERE   item_id IN (*note 2*) AND association = 'creation'<p
class="MsoNormal">                               ) AS creation_events<p class="MsoNormal">                       
ON     event_locations.event_id = creation_events.id<p class="MsoNormal">                GROUP BY
immediate_descendants.ancestor_id<pclass="MsoNormal">                ) AS location_ids ON locations.id =
location_ids.id<pclass="MsoNormal"> <p class="MsoNormal">*<b>note 1</b>* - the id of the parent location.<p
class="MsoNormal">*<b>note2</b>* - the query which returns a list of constrained item ids<p class="MsoNormal"> <p
class="MsoNormal">Thisworks but I am looking for any way to improve the performance of the query (including changing
thelayout of the tables). Any ideas, suggestions or general pointers would be greatly appreciated.<p
class="MsoNormal"> <pclass="MsoNormal">Thanks very much,<p class="MsoNormal">Ryan</div>